Create the Code Behind the User Form

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

The Declarations section of the dlgPrintSheets UserForm should contain declarations of the module-level variables, as shown in Example 18-1.

Example 18-1. Module-Level Variable Declarations

Option Explicit

Dim cSheets As Integer

Dim sSheetNames() As String Cancel button code

The Cancel button code is shown in Example 18-2. Example 18-2. The cmdCancel_Click Event Handler

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 source code is shown in Example 18-3.

Example 18-3. The cmdPrint_Click Event Handler

Private Sub cmdPrint Click() PrintSelectedSheets Unload Me End Sub The Form's Initialize event

The Initialize event of the UserForm is the place to fill the list box with a list of sheets. Our application uses a module-level array, sSheetNames, to hold the sheet names and a module-level integer variable, cSheets, to hold the sheet count; both were defined in Example 18-1. We fill these variables in the Initialize event and then use the array to fill the list, as Example 18-4 shows. The variables 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 sheets there are in the workbook.

Example 18-4. The UserForm's Initialize Event Procedure

Private Sub UserForm Initialize() Dim ws As Object 'Worksheet

ReDim sSheetNames(1 To 10)

lstSheets.Clear cSheets = 0

For Each ws In ActiveWorkbook.Sheets cSheets = cSheets + 1

' Redimension arrays if necessary If UBound(sSheetNames) < cSheets Then

ReDim Preserve sSheetNames(1 To cSheets + 5) End If

' Save name of sheet sSheetNames(cSheets) = ws.Name

' Add sheet name to list box lstSheets.AddItem sSheetNames(cSheets) Next End Sub The PrintSheets procedure

The main printing procedure is shown in Example 18-5. Note that we have been careful to deal with two special cases. First, there may not be any sheets in the workbook. Second, the user may hit the Print button without selecting any sheets in the list box.

It is important to 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 18-5. The PrintSelectedSheets Procedure

Sub PrintSelectedSheets() Dim i As Integer Dim bNoneSelected As Boolean bNoneSelected = True

If cSheets = 0 Then

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

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

' List box is 0-based, arrays are 1-based ActiveWorkbook.Sheets(sSheetNames(i + 1)).PrintOut End If Next End If

If bNoneSelected Then MsgBox "No sheets have been selected from the list box.", vbExclamation End If End Sub

0 0

Post a comment