Microsoft Office Tutorials and References
In Depth Information The Form's Initialize event
Private Sub cmdPrint_Click()
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)
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)
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
Search JabSto ::

Custom Search