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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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).
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top