I've followed the instructions found on this page:
http://www.computorcompanion.com/LPMArticle.asp?ID=221
Adding this code to my workbook:
Dim sheetChart As New ChartWithEvents
Dim embeddedCharts() As New ChartWithEvents
Dim hasCharts As Boolean
Private Sub Workbook_SheetActivate(ByVal aSheet As Object)
'Enable events for all charts embedded on a sheet
MsgBox "Activating " & aSheet.Name
If TypeName(ActiveSheet) = "Chart" Then
Set sheetChart.ch = aSheet
MsgBox "Activating events for " & sheetChart.ch.Name
End If
If aSheet.ChartObjects.Count > 0 Then
ReDim embeddedCharts(aSheet.ChartObjects.Count)
Dim aChart As ChartObject
Dim i As Integer
i = 1
For Each aChart In ActiveSheet.ChartObjects
Set embeddedCharts(i).ch = aChart.Chart
MsgBox "Activating events for " & embeddedCharts(i).ch.Name
i = i + 1
Next
hasCharts = True
Else
hasCharts = False
End If
End Sub
Private Sub Workbook_SheetDeactivate(ByVal aSheet As Object)
'Disable events for all charts embedded on a sheet
MsgBox "Deactivating " & aSheet.Name
If Not sheetChart.ch Is Nothing Then
MsgBox "Deactivating events for " & sheetChart.ch.Name
End If
Set sheetChart.ch = Nothing
If hasCharts Then
For i = 1 To UBound(embeddedCharts)
If Not embeddedCharts(i).ch Is Nothing Then
MsgBox "Deactivating events for " & embeddedCharts(i).ch.Name
End If
Set embeddedCharts(i).ch = Nothing
Next
End If
End Sub
And added a class 'ChartWithEvents' containing this code:
Public WithEvents ch As Chart
Private Sub ChartWithEvents_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
MsgBox "Select"
If ElementID = xlSeries Then
If Arg2 > 0 Then
MsgBox ch.Name & ": " & "S" & Arg1 & "P" & Arg2
End If
End If
End Sub
However, when I click on a point, I don't get the message box. Nor do I get the message box 'Select'. I do get the message boxes when activating and deactivating a worksheet (and supposedly activating/deactivating events).
Why is 'ChartWithEvents_Select' not working?
Also, how come I don't get 'Workbook_SheetActivate' when I first open the Excel workbook? And how do I trigger on this occassion to do proper setup?
Bookmarks