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?

Solution 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

Solution 2

Real easy. Put this VBA procedure into a regular code module:

Sub ClickChart()
  ActiveSheet.ChartObjects(Application.Caller).BringToFront
End Sub

Assign the macro ClickChart to all charts you want to have this behavior.

When you click any of the charts, it gets moved in front of all others on the sheet.

After posting, I see that @timwilliams has suggested this in a comment to another answer.

Solution 3

If I understood the question, I faced the same problem. Whenever there are several overlapping charts, their visualization precedence follows the ZOrder.

In Excel 2003, when one selected a chart, it came to the foreground (at least for visualization, I do not know whether its ZOrder was temporarily changed). When the chart was deselected, its visualization precedence returned to "normal".

Starting with Excel 2007, charts do not temporarily come to foreground for visualization when selected, so if they are buried behind other charts (or possibly other Shapes), the only option to see them in full is to bring them to front. This has two downsides: 1) more clicks are needed, 2) the (possibly intended) ZOrder is lost.

Even Jon Peltier, in a post from 5th May 2009, mentioned that there is no workaround for that.

I have attempted a solution based on:

1. Detecting the activation of a chart.
2. Storing its current ZOrder for later use.
3. Bringing it to front.
4. After deselecting the chart, restoring its original ZOrder.

This is the basic idea, and the scheme works fairly well, with a few glitches. I have actually based my code on the page by Jon Peltier quoted here by brettdj. One of the modifications is

Private Sub EvtChart_Activate()
    Application.EnableEvents = False
    ActivatedChart = EvtChart.name
    If (TypeName(EvtChart.Parent) = "ChartObject") Then
    ' Chart is in a worksheet
      Dim chObj As ChartObject
      Set chObj = EvtChart.Parent
      chObj.BringToFront
    Else
    ' Chart is in its own sheet
    End If
    Application.EnableEvents = True
End Sub

Use something similar for EvtChart_Deactivate. I hope that the idea is useful.

Comments

[Tim Williams]

You could also just assign the ChartSelected without a parameter, and use Application.Caller in the sub to get the name of the chart.

[riderBill]

@JonPeltier I copied the code above into the appropriate modules and called Set_All_Charts(). That worked ok, but when I call Reset_All_Charts I get a compiler error: "Variable not defined" on the Set clsEventChart.EvtChart = Nothing line. Isn't clsEventChart.EvtChart a property of a class? What does it mean to set a class property to Nothing (as opposed to setting an object of the class to Nothing)? Why is it necessary. Is the property a static member? Maybe you can guess from my questions that I'm fairly new to VBA.

[Jon Peltier]

EvtChart is not exactly a property. It's a public variable of type Chart declared in the class. Being type Chart, it can be set to an actual chart or to nothing.

[Jon Peltier]

The error is that the line you got the error on either doesn't belong, or the SetAllCharts needs to be more elaborate. That line is used in case the sheet itself is a chart, not a regular worksheet. I'll add this to the code I posted.

[riderBill]

@John Peltier. Thanks a lot for your help. I had commented out the offending line, which worked, but now I understand, which is better. The event handler is working. Working too well aamof! Gee there are a lot of activate and select events going on behind the scenes. I don't have a single activate or select in my code, unless using "set x =ActiveChart" counts.

[Sverrir Sigmundarson]

Excellent answer! I incorporated the code discussed here into a working demo workbook. It may make it easier for many to understand how this is supposed to work. Unfortunately I cannot attach files to answers so here is a link blog.sverrirs.com/2017/02/excel-vba-chart-events.html