Excel

Use events with an embedded chart

Chart events are automatically enabled for chart sheets but not for charts embedded in a worksheet. To use events with an embedded chart, you need to perform the following steps:

  1. Create a class module
    Give the class module a descriptive name, such as ChartClass and declare a Public Chart object. Also, write the code for event-handler procedures.
  2. Create a standard module
    Connect the declared Chart object with a single or multiple charts
  3. Open ThisWorkbook code module
    Use workbook-events to Call macros created in the standard module on step 2.

Create a Chart Class

To add a new class module, open the Visual Basic Editor (VBE) window (press Alt + F11), click Insert > Class Module from the main menu or, in the Project window, right-click on the VBAProject and click insert > Class Module.

Next, rename the class module, use the Properties window to give the class module a descriptive name (such as ChartClass).

The next step is to declare a Public variable, Obj, that will represent the Chart object in the class module using the WithEvents keyword:

Option Explicit
Public WithEvents Obj As Chart

After the Obj variable has been declared with the WithEvents keyword, it appears in the Object drop-down list box in the class module. When you select the Obj in the Object box, the events for the charts are listed in the Procedure drop-down box on the right:

For example, the Obj_Activate procedure is executed when the chart is activated. 

Option Explicit
'Obj: The chart object
Public WithEvents Obj As Chart

'WorkbookName: Stores the workbook name
Public WorkbookName As String

'The chart activate event procedure
Private Sub Obj_Activate()
 MsgBox Obj.Name
End Sub

Next, create a standard module and connect the chart object, Obj, with a chart.

Create a Standard Module

Before your event handler procedures will run, you must connect the declared object in the class module with your embedded chart. You do this by declaring an object of type ChartClass in a standard module. To add a new standard module, click Insert > Module from the main menu or, in the Project window, right-click on the VBAProject and click insert > Module and write the following code:

Option Explicit
Dim Collect As New Collection

'If nothing passed then enable chart events for the entire workbook
Sub ChartEvents(Optional sh As Object)
 If sh Is Nothing Then
  Dim i As Integer
  For i = 1 To ActiveWorkbook.Sheets.Count
   Call Activate(ActiveWorkbook.Sheets(i))
  Next i
 Else
  Call Activate(sh)
 End If
End Sub

'Activate chart events for a sheet
Private Sub Activate(sheet As Object)
 Dim o As ChartClass
 If TypeName(sheet) = "Chart" Then
  Set o = New ChartClass
  Set o.Obj = sheet
  o.WorkbookName = ActiveWorkbook.FullName
  Collect.Add o

 ElseIf sheet.ChartObjects.Count > 0 Then
  Dim c As ChartObject
  For Each c In sheet.ChartObjects
   Set o = New ChartClass
   Set o.Obj = c.Chart
   o.WorkbookName = ActiveWorkbook.FullName
   Collect.Add o
  Next c
 End If
End Sub

'Deactivate chart events for a workbook
Sub DeActivateChartEvents(wb As Workbook)
 Dim i As Long
 i = Collect.Count
 Do While i > 0
  If Collect.Item(i).WorkbookName = wb.FullName Then
   Set Collect.Item(i).Obj = Nothing
   Collect.Remove i
  End If
  i = i - 1
 Loop
End Sub

Enable chart events for a particular embedded chart

Open ThisWorkbook code module and enter the following code:

Option Explicit
Private Sub Workbook_Open()
 '1st chart on Sheet1
  Call ChartEvents(Sheets("Sheet1").ChartObjects(1).Chart)
End Sub

The Workbook_Open procedure runs when you open the workbook, so you must close and re-open the workbook to Call the ChartEvents procedure (which we created in the standard module). The above example enables chart-events for the first chart on the first sheet .

Enable chart events for all charts in a specific worksheet

Open ThisWorkbook code module and enter the following code:

Option Explicit
Private Sub Workbook_Open()
 Call ChartEvents(Sheets("Sheet1"))
End Sub

The Workbook_Open procedure runs when you open the workbook, so you must close and re-open the workbook to Call the ActivateChartEvents procedure (which we created in the standard module). The above example enables chart-events for all embedded-charts on the Sheet1. If the Sheet1 is a chart-sheet, the chart-events will also be enabled for that chart sheet.

Enable chart events for all worksheets

To enable chart events for all worksheets in the active workbook, just remove the optional sheet argument when calling the ChartEvents procedure:

Option Explicit
Private Sub Workbook_Open()
 Call ChartEvents
End Sub

Enable Chart events for all workbooks

To enable chart events for all available workbooks you need to use the Application Level Events, see how to enable application-level events. We’ll use the WorkbookActivate event which enables the chart events for the active workbook and use the WorkbookDeactivate event to deactivate chart events, see the following code: 

Option Explicit
Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

'Enable chart events for the active workbook
Private Sub App_WorkbookActivate(ByVal wb As Workbook)
 Call ChartEvents
End Sub

'Deactivate chart events when the workbook is out of focus
Private Sub App_WorkbookDeactivate(ByVal wb As Workbook)
 Call DeActivateChartEvents(wb)
End Sub

Calling the DeActivateChartEvents procedure in the WorkbookDeactivate event, clears the memory by deactivating the chart events for an inactive workbook. You can use the WorkbookOpen event if you don't want to deactivate the chart events for the deactivated workbook. See following example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
 Call ChartEvents
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

To learn about the Application events, see Application Level Events.