Create the Code Behind the User Form

Now it is time to create the code behind these controls.

20.12.2.1 The Declarations section

The Declarations section should contain declarations of the module-level variables as shown in Example 20-6.

Example 20-6. Module-Level Variables in the user form's Declarations Section

Dim cPTs As Integer Dim sPTNames() As String Dim sSheets() As String

20.12.2.2 Cancel button code

The Cancel button code is shown in Example 20-7.

Example 20-7. The cmdCancel_Click Event Procedure

Private Sub cmdCancel Click()

Unload Me End Sub

20.12.2.3 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() PrintSelectedPTs Unload Me End Sub

20.12.2.4 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 lstPTs.Clear 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

Tea347 ly®

' Add item to list box lstPTs.Addltem PT.Name & " ( in " & _ sSheets(cPTs) & ")"

Next Next End Sub

20.12.2.5 PrintPTs procedure

The main printing procedure is shown in Example 20-10. Note that we have been careful to deal with two special cases. First, there may not be any pivot tables in the workbook. Second, the user may hit the Print button without selecting any pivot tables in the list box. Note also that list boxes are 0-based, meaning that the first item is item 0. However, our arrays are 1-based (the first item is item 1), so we must take this into account when we move from a selection to an array member; to wit, selection i corresponds to array index i+1.

Example 20-10. The PrintSelectedPTs Procedure

Sub PrintSelectedPTs()

' Print the selected pivot tables in lstPTs

Dim i As Integer

Dim bNoneSelected As Boolean bNoneSelected = True

MsgBox "No pivot tables in this workbook.", vbExclamation Exit Sub Else

For i = 0 To lstPTs.ListCount - 1 If lstPTs.Selected(i) Then bNoneSelected = False

' List box is 0-based, arrays are 1-based Worksheets(sSheets(i + 1)).

PivotTables(sPTNames(i + 1)). TableRange2.PrintOut

End If Next End If

If bNoneSelected Then

MsgBox "No pivot tables have been selected.", vbExclamation End If End Sub

0 0

Post a comment