excel - Catch event on chart click

Question

I need to catch event in Excel VBA when I click on the chart.

I want to bring the chart to the front, when it is activated, but I can't find an appropriate event.

For the chart not on sheet (separate, fullscreen chart) there is Chart_Activate() event.

How can I call the same event when the chart is on a certain sheet?

Answer 1

If you have a collection of charts to automate, either on particular sheet(s), or the entire workbook then I would suggest that you use a class module to capture rather than tie in code chart by chart

Jon Peltier (as per normal) has covered this charting code option in great detail, see Chart Events in Microsoft Excel.

In a class module called CEventChart put:

Option Explicit
' Declare object of type "Chart" with events
Public WithEvents EvtChart As Chart    

Private Sub EvtChart_Activate()
EvtChart.ChartObjects msoBringToFront
End Sub

In a normal module put

Option Explicit

Dim clsEventChart As New CEventChart
Dim clsEventCharts() As New CEventChart

Sub Set_All_Charts()
    ' Enable events on sheet if it is a chart sheet
    If TypeName(ActiveSheet) = "Chart" Then
        Set clsEventChart.EvtChart = ActiveSheet
    End If

    ' Enable events for all charts embedded on a sheet
    ' Works for embedded charts on a worksheet or chart sheet
    If ActiveSheet.ChartObjects.Count > 0 Then
        ReDim clsEventCharts(1 To ActiveSheet.ChartObjects.Count)
        Dim chtObj As ChartObject
        Dim chtnum As Integer

        chtnum = 1
        For Each chtObj In ActiveSheet.ChartObjects
            ' Debug.Print chtObj.Name, chtObj.Parent.Name
            Set clsEventCharts(chtnum).EvtChart = chtObj.Chart
            chtnum = chtnum + 1
        Next ' chtObj
    End If
End Sub

Sub Reset_All_Charts()
    ' Disable events for all charts previously enabled together
    Dim chtnum As Integer
    On Error Resume Next
    Set clsEventChart.EvtChart = Nothing
    For chtnum = 1 To UBound(clsEventCharts)
        Set clsEventCharts(chtnum).EvtChart = Nothing
    Next ' chtnum
End Sub

then run Set_All_Charts with the sheet selected where you want your charts to be sent to the front, Jon uses these sheet events to set and disable the chart code on a particular sheet

Private Sub Worksheet_Activate()
    Set_All_Charts
End Sub

Private Sub Worksheet_Deactivate()
    Reset_All_Charts
End Sub