Hi,

I am experimenting with trapping chart events using class modules. I
have a class module 'ChartEvents' that traps the double click event for
charts, and a normal module with the following code:

Option Explicit
Dim clsCharts As New Collection

Public Sub InitialiseChartEvents()
Dim wks As Worksheet
Dim iChart As ChartObject
Dim aChart As ChartEvents

For Each wks In ActiveWorkbook.Worksheets
For Each iChart In wks.ChartObjects
Set aChart = New ChartEvents
Set aChart.aChart = wks.ChartObjects(iChart.Index).Chart
Call clsCharts.Add(aChart)
Debug.Print ThisWorkbook.Name & " - " & wks.Name & " - " &
iChart.Name
Next iChart
Next wks

End Sub

ChartEvents module has following declaration (the event trapping code
is irrelevant i think):
Public WithEvents aChart As Chart



So basically the code loops through each worksheet, and adds every
chart in the workbook to the clsCharts collection, as a member of the
ChartEvents class.

The problem is, I want the user to be able to add a new chart, then
click a button that re-runs this routine again, so that the new chart
is included in the collection. But if I do that, then it seems it adds
a second copy of all the original charts, and so if i double click on
one of these charts, it runs the trapped event code twice.... and if i
run the InitialiseChartEvents code 10 times, it will have added 10
copies, and runs the double click code 10 times!

So first of all: Is there a better way of adding new items to a
collection just once? Or should this be done without using a collection
at all (if so how)?

But secondly, and to be honest more importantly to my understanding of
class modules and collections, can I write the InitialiseChartEvents
code so that it terminates the entire ChartEvents class, or just kills
the clsCharts collection, up front and then re-adds all the charts from
scratch? This seemed to me to be this easiest fix, and I've searched
and searched for how to do this but to no avail. Probably i'm missing
something really dumb.... or maybe its just impossible.

Any help on both points much appreciated.

Stuart