Same event, multiple sheets?
 

What's the best approach to implementing a Chart_Select event on all
charts in a workbook? And is there any way I can set it up through a
macro?

I have a file of tab delimited data that I will use as the basis for
generating a collection of 28 charts; I have a macro installed as a
toolbar button that adds and formats the charts. Each chart has five
series on it, and it would be nice to be able to hide one or more of
those series to make the others more readable. The easiest way I've
found to do this is by using the Chart_Select event to detect clicks
on the legend for each series, and toggle visibility based on that. It
works just fine for one chart, but I don't know how to add the event
from scratch - starting with a tab delimited file, remember - or if it
can be added in such a way as to make it happen on all charts.

Any suggestions?

Thanks

Robert Smith

Hi Robert,

The easiest way I've
found to do this is by using the Chart_Select event to detect clicks
on the legend for each series, and toggle visibility based on that. It
works just fine for one chart, but I don't know how to add the event
from scratch - starting with a tab delimited file, remember - or if it
can be added in such a way as to make it happen on all charts.

One way is to use a class module:

Class CChartEvents

Public WithEvents mchtChart As Chart

Private Sub mchtChart_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

' your code
End Sub


Then in a standard module, create a new instance of this class for each
chart sheet:

Dim mEventClasses As Collection

Sub SetUpChartEvents()

Dim chtChart As Chart
Dim clsChartEvents As CChartEvents

Set mEventClasses = New Collection

For Each chtChart In ThisWorkbook.Charts
Set clsChartEvents = New CChartEvents
Set clsChartEvents.mchtChart = chtChart
mEventClasses.Add clsChartEvents
Next

End Sub


With this approach, you could store the standard and class modules a
text files and programmatically add them to any workbooks you generate.
(see Chip's VBE page for details at www.cpearson.com).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk