Embedded charts with events not working?

Joined
Apr 18, 2012
Messages
1
Reaction score
0
I've followed the instructions found on this page:
http://www.computorcompanion.com/LPMArticle.asp?ID=221

Adding this code to my workbook:

Code:
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:
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 firing?

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top