I have researched for a couple of days on ways to fire a routine when a user clicks on a point in the chart. I have tried everything, I even found a sample spreadsheet with all the code, and it doesn't work.
on the sheet with charts
the code for EnableEventsForAllCharts
the code in the class module:
The code never fires, no matter what I do. I am missing something here. Also, the end goal is to fire a routine when a user clicks on a specific point on the chart, to then chart the detail months data for that particular item. Any help would be GREATLY appreciated. I did see a link on another question to a tutorial on working with points on a chart, but that link is broken.
on the sheet with charts
VBA Code:
Option Explicit
'****************************************************************
' When the worksheet is selected hook up the event classes.
' If the chart source data has changed in the mean time then the Calculate event will be raised
Private Sub Worksheet_Activate()
EnableEventsForAllCharts
End Sub
the code for EnableEventsForAllCharts
VBA Code:
' *************************************************
' This function connects all chart objects in the currently active sheet to the event enabled
' class module CEventChart. This class module contains functions to handle event callbacks raised
' by the chart object when either the user or excel interacts with it.
Sub EnableEventsForAllCharts()
' 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
Set clsEventCharts(chtnum).EvtChart = chtObj.Chart
chtnum = chtnum + 1
Next ' chtObj
End If
End Sub
the code in the class module:
VBA Code:
Option Explicit
' Declare object of type "Chart" with events
' This object will hold the Chart instance that is currently being manipulated
Public WithEvents EvtChart As Chart
' *********** The Excel event functions go below this line, avoid having too much custom code in these functions
' rather have the event functions call them as private functions (e.g. like MyGreatFunction below)
Private Sub EvtChart_Activate()
' Executed whenever the chart is enabled (e.g. users selects it)
' TODO: Here comes your logic...
MyGreatFunction
End Sub
Private Sub EvtChart_Calculate()
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False
' TODO: Here comes your logic...
MyGreatFunction
'Enable screen updating again
Application.ScreenUpdating = True
End Sub
' + any other evens you want to subscribe to, see: https://msdn.microsoft.com/en-us/library/office/ff822192.aspx
'************* Your private functions go below this line
Private Sub MyGreatFunction()
' You can access the chart object here by using the EvtChart object
Debug.Print "Chart: " + EvtChart.Name
End Sub
The code never fires, no matter what I do. I am missing something here. Also, the end goal is to fire a routine when a user clicks on a specific point on the chart, to then chart the detail months data for that particular item. Any help would be GREATLY appreciated. I did see a link on another question to a tutorial on working with points on a chart, but that link is broken.