Microsoft Office Tutorials and References
In Depth Information
Example 20-7. The cmdCancel_Click Event Procedure
Example 20-7. The cmdCancel_Click Event Procedure
Private Sub cmdCancel_Click()
Unload Me
End Sub Print button code
The Print button calls the main print procedure and then unloads the form; its event code is shown
in Example 20-8 .
Example 20-8. The cmdPrint_Click Event Procedure
Private Sub cmdPrint_Click()
Unload Me
End Sub The Form's Initialize event
The user form's Initialize event is the place to fill the list box with a list of pivot tables. Our
application uses two module-level arrays: one to hold the worksheet names and one to hold the
pivot-table names. There is also a module-level variable to hold the pivot-table count. We fill
these arrays in the Initialize event, as shown in Example 20-9 , and then use the arrays to fill the
list. These arrays are used again in the main print procedure, which is why we have declared them
at the module level.
Note the use of the ReDim statement to redimension the arrays. This is necessary since we do not
know at the outset how many pivot tables there are in the workbook.
Example 20-9. The Initialize Event
Private Sub UserForm_Initialize()
' Fill lstPTs with the list of pivot tables
Dim ws As Worksheet
Dim PT As PivotTable
ReDim sPTNames(1 To 10) As String
ReDim sSheets(1 To 10) As String
cPTs = 0
For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
' Update PT count
cPTs = cPTs + 1
' Redimension arrays if necessary
If UBound(sSheets) < cPTs Then
ReDim Preserve sSheets(1 To cPTs + 5)
ReDim Preserve sPTNames(1 To cPTs + 5)
End If
' Save name of pivot table and ws
sPTNames(cPTs) = PT.Name
sSheets(cPTs) = ws.Name
Search JabSto ::

Custom Search