vba 捕捉图表点击事件

Catch event on chart click

提问by Nick

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

单击图表时，我需要在 Excel VBA 中捕获事件。

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.

对于不在工作表上的图表（单独的全屏图表）有Chart_Activate()事件。

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

当图表位于某个工作表上时，如何调用相同的事件？

回答by brettdj

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.

Jon Peltier（按照常规）非常详细地介绍了此图表代码选项，请参阅Microsoft Excel 中的图表事件。

In a class module called CEventChart put:

在名为 CEventChart 的类模块中放置：

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

然后运行 ​​Set_All_Charts 并选择您希望将图表发送到前面的工作表，Jon 使用这些工作表事件来设置和禁用特定工作表上的图表代码

Private Sub Worksheet_Activate()
    Set_All_Charts
End Sub

Private Sub Worksheet_Deactivate()
    Reset_All_Charts
End Sub

回答by sancho.s ReinstateMonicaCellio

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

如果我理解了这个问题，我就会遇到同样的问题。每当有多个重叠图表时，它们的可视化优先级遵循ZOrder.

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

In Excel 2003, when one selected a chart, it came to the foreground (at least for visualization, I do not know whether its ZOrderwas temporarily changed). 取消选择图表后，其可视化优先级恢复为“正常”。

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) ZOrderis lost.

从 Excel 2007 开始，图表在被选中时不会暂时出现在可视化的前景中，因此如果它们被隐藏在其他图表（或可能是其他图表）后面Shape，则查看它们完整的唯一选择是将它们放在前面。这有两个缺点：1）需要更多的点击，2）（可能是有意的）ZOrder丢失了。

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

甚至Jon Peltier在2009 年 5 月 5 日的帖子中也提到没有解决方法。

I have attempted a solution based on:

我尝试了基于以下的解决方案：

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

1. 检测图表的激活。
2. 存储其电流ZOrder以备后用。
3. 把它带到前面。
4. 取消选择图表后，恢复其原始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 Peltierquoted here by brettdj. One of the modifications is

这是基本思想，该方案运行良好，但有一些小故障。实际上，我的代码基于brettdj 在此引用的 Jon Peltier的页面。其中一项修改是

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.

使用类似的东西EvtChart_Deactivate。我希望这个想法是有用的。

回答by Jon Peltier

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

真容易。将此 VBA 过程放入常规代码模块中：

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

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

将宏分配ClickChart给您希望具有此行为的所有图表。

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.

发布后，我看到@timwilliams 在对另一个答案的评论中提出了这一建议。

回答by GSerg

Create a handler in the worksheet:

在工作表中创建一个处理程序：

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

Right-click the chart and select Assign macro, then type something like

右键单击图表并选择分配宏，然后键入类似

'Sheet1.ChartSelected "Chart 1"'

'Sheet1.ChartSelected "Chart 1"'

where Chart 1is the name of the chart.

Chart 1图表的名称在哪里。

To assign this handler programmatically, use

要以编程方式分配此处理程序，请使用

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