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

I did it without using a collection. My class included a link to the workbook
so that I could catch when a sheet was being activated. If that sheet was a
chart then I pointed my chart object at that sheet. This one only works for
Chart Sheets but I suppose that by using the selection change event of the
workbook you could determine when a chart emebeded in a sheet was
activated... Here is the code for the class... (This adds a linear trend line
when a series is double clicked)

Option Explicit

Private WithEvents cht As Chart
Private WithEvents wbk As Workbook

Private Sub cht_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As
Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim objTrendLine As Trendline

Cancel = True
If ElementID = 3 Then
On Error Resume Next
cht.SeriesCollection(Arg1).Trendlines(1).Delete
On Error GoTo 0
Set objTrendLine =
cht.SeriesCollection(Arg1).Trendlines.Add(Type:=xl Linear)
With objTrendLine.Border
.Weight = xlThin
.LineStyle = xlDot
.Color = cht.SeriesCollection(Arg1).Border.Color
End With
ElseIf ElementID = 8 Then
cht.SeriesCollection(Arg1).Trendlines(1).Delete
End If
End Sub

Private Sub Class_Initialize()
On Error Resume Next
Set cht = ThisWorkbook.ActiveChart
Set wbk = ThisWorkbook
On Error GoTo 0

End Sub

Private Sub wbk_SheetActivate(ByVal Sh As Object)
If TypeName(Sh) = "Chart" Then
Set cht = Sh
Else
Set cht = Nothing
End If
End Sub

--
HTH...

Jim Thomlinson


" wrote:

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