Microsoft Office Tutorials and References
In Depth Information
Example 21-8. The UserForm's Initialize Event Procedure
The UserForm's Initialize event, which is shown in Example 21-8 , is the place to fill the list box
with a list of embedded charts. Our application uses a module-level array to hold the chart names,
a module-level array to hold the ChartObject object names, and a module-level integer variable to
hold the chart count. We fill these variables in the Initialize event and then use the arrays to fill the
list. 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 embedded charts there are in the workbook.
Example 21-8. The UserForm's Initialize Event Procedure
Private Sub UserForm_Initialize()
' Fill lstCharts with the list of embedded charts
Dim ws As Worksheet
Dim chObj As ChartObject
ReDim sChartObjNames(1 To 10) As String
ReDim sSheets(1 To 10) As String
cCharts = 0
For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
' Update chart count
cCharts = cCharts + 1
' Redimension arrays if necessary
If UBound(sSheets) < cCharts Then
ReDim Preserve sSheets(1 To cCharts + 5)
ReDim Preserve sChartObjNames(1 To cCharts + 5)
End If
' Save name of chart and ws
sChartObjNames(cCharts) = chObj.Name
sSheets(cCharts) = ws.Name
' Add item to list box
If chObj.Chart.HasTitle Then
lstCharts.AddItem chObj.Chart.ChartTitle.Text & " (" & _
sChartObjNames(cCharts) & " in " & sSheets(cCharts) & ")"
lstCharts.AddItem "<No Title> (" & sChartObjNames(cCharts)
& " in " & _
sSheets(cCharts) & ")"
End If
End Sub The PrintCharts procedure
The main printing procedure is shown in Example 21-9 . Note that we have been careful to deal
with two special cases. First, there may not be any embedded charts in the workbook. Second, the
user may hit the Print button without selecting any charts in the list box.
Search JabSto ::

Custom Search