Chart Events

[shfish333]

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

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

' *************************************************
' 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:

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.

 

[Jon Peltier]

Is the chart embedded in a worksheet, or is it a standalone chart?

You don't show any event procedure in the class module code which would fire when a point is clicked on (e.g., _Select, _MouseDown, _Mouseup).

 

[shfish333]

Is the chart embedded in a worksheet, or is it a standalone chart?

You don't show any event procedure in the class module code which would fire when a point is clicked on (e.g., _Select, _MouseDown, _Mouseupembee

Embedded in a worksheet. I'm not sure how to do that. There are 2 procedures in there, activate and calculate. Neither fire. I have changed the data that should have prompted the _Calculate procedure, and it does not. Can I post a file on here?

 

[shfish333]

Embedded in a worksheet. I'm not sure how to do that. There are 2 procedures in there, activate and calculate. Neither fire. I have changed the data that should have prompted the _Calculate procedure, and it does not.

 

[shfish333]

Figured out a very simple solution:

Public WithEvents CHT As Chart

Private Sub CHT_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

Dim strItemName As String

If ElementID = 3 Then

strItemName = ActiveChart.SeriesCollection(Arg1).Name

ChangeMonthlyTrendReport (strItemName)



ThisWorkbook.Sheets("Dashboard").Cells(1, 1).Select

End If


End Sub

Private Sub Workbook_Open()
    Set CHT = Worksheets("Dashboard").ChartObjects("chtTop10").Chart
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 Set CHT = Worksheets("Dashboard").ChartObjects("chtTop10").Chart
End Sub

Sub ChangeMonthlyTrendReport(ByVal strItem As String)

ThisWorkbook.Sheets("dbPivots").PivotTables("pvtItemTrend").PivotFields("Item").CurrentPage = strItem
ThisWorkbook.Sheets("Dashboard").ChartObjects("chtRevTrend").Activate

Set objChrt1 = Sheets("Dashboard").ChartObjects("chtRevTrend")
Set myChart1 = objChrt1.Chart
myChart1.ChartTitle.Text = strItem

End Sub