How to: Make Excel charts responding to events

This Excel VBA code is to enable events on Excel embedded charts.

1. 1. Create a class

2. Type in the code for the class. In this example, the VBA code is catching the BeforeDoubleClick event

Private WithEvents CEvents As Chart

Private Sub CEvents_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    Dim vCats As Variant
    Dim a As Variant
    If (ElementID = xlSeries Or ElementID = xlDataLabel) And Arg2 >= 1 Then
        vCats = CEvents.SeriesCollection(Arg1).XValues
        a = vCats(Arg2)
        MsgBox Format(a, "mm/dd/yyyy")
        Cancel = True
    End If
End Sub

Private Sub Class_Initialize()
    Set CEvents = Sheets("Sheet1").ChartObjects("Chart 1").Chart
End Sub

3. Initialize the class to connect to the chart

Dim a As ClassChart

Private Sub Workbook_Activate()
    Set a = New ClassChart
End Sub