Example The User Forms 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 lstCharts.Clear 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) & ")" Else lstCharts.AddItem "<No Title> (" & sChartObjNames(cCharts)

End If Next Next End Sub

0 0

Post a comment