Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
880 views
in Technique[技术] by (71.8m points)

excel - Catch event on chart click

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...