在图表上点击捕捉事件 [英] Catch event on chart click

问题描述

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

我想把图表带到前面，当它被激活时，但是我找不到适当的事件。

对于不在表格上的图表（单独的全屏图），有 Chart_Activate（）事件

当图表在某张表单上时，如何调用相同的事件？

解决方案

如果您有一组图表自动化，无论是在特定表单或整个工作簿上，我建议您使用类模块来捕获而不是绑定代码图表按图表

Jon Peltier（按照正常）已经非常详细地涵盖了这个图表代码选项，请参见 Microsoft Excel中的图表事件。

在一个名为CEventChart的类模块中： >

  Option Explicit 
'声明类型的对象Chart与事件
 Public WithEvents EvtChart As Chart 
 
 Private Sub EvtChart_Activate（）
 EvtChart.ChartObjects msoBringToFront 
 End Sub 
  

在正常的模块中，

 显式
 
 Dim clsEventChart作为新的CEventChart 
 Dim clsEventCharts（）作为新的CEventChart 
 
 Sub Set_All_Charts（）
'如果它是一个图表单
如果TypeName（ActiveSheet）=图表然后
设置clsEventChart.EvtChart = ActiveSheet 
结束如果
 
'为嵌入在表单上的所有图表启用事件
'适用于工作表或图表表上的嵌入式图表
如果ActiveSheet.ChartObjects.Count> 0然后
 ReDim clsEventCharts（1到ActiveSheet.ChartObjects.Count）
 Dim chtObj As ChartObject 
 Dim chtnum As Integer 
 
 chtnum = 1 
对于每个chtObj在ActiveSheet.ChartObjects 
'Debug.Print chtObj.Name，chtObj.Parent.Name 
设置clsEventCharts（chtnum）.EvtChart = chtObj.Chart 
 chtnum = chtnum + 1 
 Next'chtObj 
 End If 
 End Sub 
 
 Sub Reset_All_Charts（）
'禁用以前启用的所有图表的事件
 Dim chtnum As Integer 
 On Error Resume Next 
 Set clsEventChart.EvtChart = Nothing 
对于chtnum = 1到UBound（clsEventCharts）
设置clsEventCharts（chtnum）.EvtChart = Nothing 
下一步'chtnum 
 End Sub 
  

然后运行Set_All_Charts，选择要将图表发送到的工作表前面，Jon使用这些sheet事件进行设置和d可以在特定表单上的图表代码

  Private Sub Worksheet_Activate（）
 Set_All_Charts 
 End Sub 
 
 Private Sub Worksheet_Deactivate（）
 Reset_All_Charts 
 End Sub 
  

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?

解决方案

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

这篇关于在图表上点击捕捉事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！