Catch an event on the chart

Question

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

I want to bring the chart to the forefront when it is activated, but I cannot find a suitable event.

For a chart not on a sheet (a separate, full-screen chart), there is a Chart_Activate() event.

How can I trigger the same event when the chart is on a specific sheet?

Answer 1

If you have a collection of diagrams for automation, either on separate sheets or in the whole book, I would suggest that you use the class module to capture, rather than link the code diagram to the diagram

John Peltier (as usual) examined this diagram code diagram in detail; see Diagram Events in Microsoft Excel .

In a class module named 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 the 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 selected sheet where you want your charts to be sent to the front, John uses these sheet events to set and disable the chart code on a specific sheet.

 Private Sub Worksheet_Activate() Set_All_Charts End Sub Private Sub Worksheet_Deactivate() Reset_All_Charts End Sub 

Answer 2

If I understood the question, I ran into the same problem. Whenever there are several overlapping diagrams, their visualization priority follows ZOrder .

In Excel 2003, when you selected a chart, it appeared in the foreground (at least for visualization, I don’t know if its ZOrder was temporarily changed). When the chart was canceled, its visualization priority returned to "normal."

Starting with Excel 2007, graphs are not brought to the forefront for visualization when they are selected, therefore, if they are buried behind other diagrams (or, possibly, with other Shape s), the only option to see them completely is to bring them in front. This has two drawbacks: 1) it takes more clicks, 2) the lost (possibly intended) ZOrder .

Even John Peltier , in May 5, 2009 , noted that there is no workaround for this.

I tried to find a solution based on:

• Chart activation detection.
• Saving the current ZOrder for later use.
• Bringing it forward.
• Deselecting the chart, restoring the original ZOrder .

This is the main idea, and the circuit works pretty well , with a few glitches. I really based my code on the Jon Peltier page provided here by Brettj. 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 this idea is helpful.

Answer 3

Create a handler on the sheet:

 Public Sub ChartSelected(ByVal Name As String) Me.ChartObjects(Name).BringToFront End Sub 

Right-click on the chart and select Assign Macro, then enter something like

'Sheet1.ChartSelected "Chart 1"'

where Chart 1 is the name of the chart.

To assign this handler programmatically, use

 ChartObject.OnAction = "'Sheet1.ChartSelected ""Chart 1""'" 

Answer 4

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

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

Assign the ClickChart macro to all the charts you want to get.

When you click on a chart, it moves in front of everyone else on the sheet.

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