Handling chart events in Excel 2010 and newer

How to create enhanced chart interations in VBA in Excel 2010 and newer

Excel by Sverrir Sigmundarson

Enabling VBA code in Excel to receive and handle chart events is not very straight forward to do. Fortunately there are quite a few useful guides available for this, e.g. Jon Peltier and David J. Brett.

I recently had to do some custom VBA coding in Excel 2010 and a complete working demo would have been immensely useful. So here is it for anyone that might want to hit the ground running, so to say.

Get the example workbook

First of all you can download the example workbook here (it's macro enabled).

To view the code open the VBA Editor (Alt+F11).

How it is organized

Organization of the sample workbook code

Organization of the sample workbook code

The Class Module CEventChart

This is the main logic where you should put most of your code. This class module acts as the receiver that Excel calls when the charts receive events. Here you need to put the event functions you want to handle (there are examples in there on how to handle both the Activate and Calculate event).

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

Module ChartEventHookups

This module handles the nitty gritty details of connecting/disconnecting the chart events in your workbook. You should not need to modify this code at all and it can be left as is.

Option Explicit

Dim clsEventChart As New CEventChart
Dim clsEventCharts() As New CEventChart

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

' *************************************************
' Unhook the event classes from the charts
Sub DisableEventsForAllCharts()
    ' 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

Excel Object Sheet2 (Charts)

This code should be placed in all worksheets that contain charts you want to receive events for. What this code does is simply connect the events when the sheet is activated first and then disconnect the events when another sheet is activated. This helps save resources and speed up calculations.

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

'****************************************************************
'When the worksheet is deselected disable the events
Private Sub Worksheet_Deactivate()
  DisableEventsForAllCharts
End Sub

Excel Object ThisWorkbook

This code handles hooking the chart events up correctly when the workbook is first opened if the workbook is opened with the chart sheet active.

Option Explicit

'****************************************************************
' When the workbook is first opened then the charts in the active sheet must
' be connected immediately otherwise out of date data might be displayed (in case the book closed unexpectedly before)
Private Sub Workbook_Open()
  ' Disable screen updating while the subroutine is run.
  Application.ScreenUpdating = False

  EnableEventsForAllCharts

  'Enable screen updating again
  Application.ScreenUpdating = True
End Sub

Get the example workbook

Again, you can download the example workbook here (it's macro enabled).

To view the code open the VBA Editor (Alt+F11).

Software Developer
For hire

Developer & Programmer with +15 years professional experience building software.


Seeking WFH, remoting or freelance opportunities.


Subscribe