User selection of charts

[Bill]

I need an approach for users to select various charts that may be on
different worksheets. The selected charts will be collected into a list for
additional processing, such as printing, moving to a common worksheet, etc.

I can't seem to find an easy to way have the users select the charts.
Suggestions?

 

[Gary Brown]

This should give you some help with how to identify the charts in a workbook...

For i = 1 To Sheets.Count
If UCase(TypeName(Sheets(i))) = "CHART" Or _
Sheets(i).ChartObjects.Count > 0 Then
Debug.Print TypeName(Sheets(i)) & " - " & _
Sheets(i).Name & " - " & _
Sheets(i).ChartObjects.Count
End If
Next i

This snippet will list sheets that are charts and identify sheets that have
charts on them.

 

[Bill]

Thanks for the response, Gary.

That was related to some code I was already using, and slightly better, so
it was helpful. But it didn't address my core challenge.

It appears my solution will be one of two things:

(1) Copy all charts in the workbook to a single worksheet, and let the user
select them there. This obviously isn't particularly efficient, but it
should be clear.
(2) Upon a (menu) request for the additional processing, add a button to
each sheet containing a chart, and have the user click the button after
selecting each chart he or she wants to include. The buttons are then
deleted after the .OnAction code is run, or for sheets where no charts were
selected, when the additional processing is run.

One of these solutions will have to suffice.

Thanks,
Bill