A Little About Events and Charts with Excel VBA

By default, events are associated with charts that are created on separate chart sheets. Let’s look at some examples of handling events related to charts.

Suppose we need to change the color of the chart area and the chart itself (located on a separate chart sheet) depending on where the mouse click occurs. To handle this event, you can use the code shown in the Chart1 module.

Another example concerns handling the mouse move event on chart sheets. Suppose we want to display additional annotations related to data points in a text box located on the chart sheet .

To implement this example, prepare on Sheet1 the corresponding data table and an annotations table. Then, in the Chart1 module, enter the code.

Linking annotation text to a chart sheet (Chart1 module)

Option Explicit
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, _
    ByVal X As Long, ByVal Y As Long)  
    Dim RowId As Long
    Dim rg1 As Long, rg2 As Long
    Dim MyText As String
    On Error Resume Next
    ActiveChart.GetChartElement X, Y, RowId, rg1, rg2  
    If RowId = xlSeries Then
        MyText = Sheets("Sheet1").Range("Note").Offset(rg2, rg1)
    Else
        MyText = "For information about Nobel Prize winners, " & _
                 "select a column in the chart."
    End If   
    ActiveChart.Shapes(1).TextFrame.Characters.Text = MyText
End Sub

Linking Events to Embedded Charts

If a chart is embedded in a worksheet, you cannot directly link events to it. In this case, you need to perform several additional steps before you can bind the required events to charts located on worksheets.

As a small example, do the following:

• Open the VBA editor and create a class module named MyEventClassModule (Insert | Class Module, then set the Name property to MyEventClassModule).
• Declare in the class module a variable of type Chart with the keyword WithEvents. After that, in the code editor, the object MyEventClassModule will appear in the object list, and all events associated with charts will appear in the event list.

Linking events to embedded charts. Class module MyEventClassModule (version 1)

Public WithEvents MyChartClass As Chart

• In the code editor, enter the code for handling the events needed for your project’s business logic. For example we link the mouse button click to a message: “Think about your next steps!”

Linking events to embedded charts. Class module MyEventClassModule (version 2)

Private Sub MyChartClass_MouseDown(ByVal Button As Long, _
    ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    MsgBox "Think about your next steps!"
End Sub

• Link the event to the embedded chart. For example, this can be done at the workbook open stage by adding to the ThisWorkbook module the code. This associates the first embedded chart on the first worksheet with the MyChartClass object. Now, when you click the mouse on this chart, the message “Think about your next steps!” will appear.

Linking events to embedded charts. ThisWorkbook module

Dim MyClassModule As New MyEventClassModule
Sub ChartInit()
    Set MyClassModule.MyChartClass = Worksheets(1).ChartObjects(1).Chart
End Sub

Private Sub Workbook_Open()
    ChartInit
End Sub

Changing Chart Type via Context Menu

As another example of using chart events for embedded charts, let’s consider a project where, when you right-click the chart, a context menu appears with the following commands: Column, Line, Doughnut, Area, Bar, and Cone. Selecting one of these changes the chart to the corresponding type.

First, the event to handle is the right-click on an embedded chart. Therefore, you need to create a class (in this case ChartEventClass) where the code to handle this event is implemented. When the workbook is opened, the instance of ChartEventClass is linked to the specific chart, the context menu is created, and the commands of this menu are assigned macros that perform the chart type change.