Chart Events

shfish333

New Member
Joined
Mar 7, 2006
Messages
9
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
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,936
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Mar 7, 2006
Messages
9
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

New Member
Joined
Mar 7, 2006
Messages
9
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

New Member
Joined
Mar 7, 2006
Messages
9
Figured out a very simple solution:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,255
Messages
5,576,992
Members
412,759
Latest member
Jackuk127
Top