Understanding Chart Events

Excel supports several events associated with charts . For example, when a chart is activated, it generates an Activate event. The Calculate event occurs after the chart receives new or changed data. You can, of course, write VBA code that gets executed when a particular event occurs.

CROSS-REFERENCE  

Refer to Chapter 19 for additional information about events.

Table 18-1. lists all the chart events.

Table 18-1: EVENTS RECOGNIZED BY THE CHART OBJECT
Open table as spreadsheet

Event	Action That Triggers the Event
Activate	A chart sheet or embedded chart is activated.
BeforeDoubleClick	An embedded chart is double-clicked. This event occurs before the default double-click action.
BeforeRightClick	An embedded chart is right-clicked. The event occurs before the default right-click action.
Calculate	New or changed data is plotted on a chart.
Deactivate	A chart is deactivated.
DragOver	A range of cells is dragged over a chart.
DragPlot	A range of cells is dragged and dropped onto a chart.
MouseDown	A mouse button is pressed while the pointer is over a chart.
MouseMove	The position of the mouse pointer changes over a chart.
MouseUp	A mouse button is released while the pointer is over a chart.
Resize	A chart is resized.
Select	A chart element is selected.
SeriesChange	The value of a chart data point is changed.

An example of using Chart events

To program an event handler for an event taking place on a chart sheet, your VBA code must reside in the code module for the Chart object. To activate this code module, double-click the Chart item in the Project window. Then, in the code module, select Chart from the Object drop-down list on the left and select the event from the Procedure drop-down list on the right (see Figure 18-14).

Figure 18-14: Selecting an event in the code module for a Chart object.

Note  

Because an embedded chart doesn't have its own code module, the procedure that I describe in this section works only for chart sheets. You can also handle events for embedded charts, but you must do some initial setup work that involves creating a class module. This procedure is described later in "Enabling events for an embedded chart."

The example that follows simply displays a message when the user activates a chart sheet, deactivates a chart sheet, or selects any element on the chart. I created a workbook with a chart sheet; then I wrote three event handler procedures named as follows:

• Chart_Activate : Executed when the chart sheet is activated.

• Chart_Deactivate : Executed when the chart sheet is deactivated.

• Chart_Select : Executed when an element on the chart sheet is selected.

CD-ROM  

This workbook, named events “ chart sheet.xlsm , is available on the companion CD-ROM.

The Chart_Activate procedure follows:

 Private Sub Chart_Activate()     Dim msg As String     msg = "Hello " & Application.UserName & vbCrLf & vbCrLf     msg = msg & "You are now viewing the six-month sales "     msg = msg & "summary for Products 1-3." & vbCrLf & vbCrLf     msg = msg & _      "Click an item in the chart to find out what it is."     MsgBox msg, vbInformation, ActiveWorkbook.Name End Sub 

This procedure simply displays a message whenever the chart is activated. See Figure 18-15.

Figure 18-15: Activating the chart causes Chart_Activate to display this message.

The Chart_Deactivate procedure that follows also displays a message, but only when the chart sheet is deactivated:

 Private Sub Chart_Deactivate()     Dim msg As String     msg = "Thanks for viewing the chart."     MsgBox msg, , ActiveWorkbook.Name End Sub 

The Chart_Select procedure that follows is executed whenever an item on the chart is selected:

 Private Sub Chart_Select(ByVal ElementID As Long, _   ByVal Arg1 As Long, ByVal Arg2 As Long)     Dim Id As String     Select Case ElementID         Case xlAxis: Id = "Axis"         Case xlAxisTitle: Id = "AxisTitle"         Case xlChartArea: Id = "ChartArea"         Case xlChartTitle: Id = "ChartTitle"         Case xlCorners: Id = "Corners"         Case xlDataLabel: Id = "DataLabel"         Case xlDataTable: Id = "DataTable"         Case xlDownBars: Id = "DownBars"         Case xlDropLines: Id = "DropLines"         Case xlErrorBars: Id = "ErrorBars"         Case xlFloor: Id = "Floor"         Case xlHiLoLines: Id = "HiLoLines"         Case xlLegend: Id = "Legend"         Case xlLegendEntry: Id = "LegendEntry"         Case xlLegendKey: Id = "LegendKey"         Case xlMajorGridlines: Id = "MajorGridlines"         Case xlMinorGridlines: Id = "MinorGridlines"         Case xlNothing: Id = "Nothing"         Case xlPlotArea: Id = "PlotArea"         Case xlRadarAxisLabels: Id = "RadarAxisLabels"         Case xlSeries: Id = "Series"         Case xlSeriesLines: Id = "SeriesLines"         Case xlShape: Id = "Shape"         Case xlTrendline: Id = "Trendline"         Case xlUpBars: Id = "UpBars"         Case xlWalls: Id = "Walls"         Case xlXErrorBars: Id = "XErrorBars"         Case xlYErrorBars: Id = "YErrorBars"         Case Else:: Id = "Some unknown thing"     End Select     MsgBox "Selection type: " & Id End Sub 

This procedure displays a message box that contains a description of the selected item. When the Select event occurs, the ElementID argument contains an integer that corresponds to what was selected. The Arg1 and Arg2 arguments provide additional information about the selected item (see the Help system for details). The Select Case structure converts the built-in constants to descriptive strings.

Note  

This is not a comprehensive listing of all items that could appear in a Chart object. That's why I include the Case Else statement.

Enabling events for an embedded chart

As I note in the preceding section, 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.

CREATE A CLASS MODULE

In the Visual Basic Editor (VBE) window, select your project in the Project window and choose Insert Class Module. This will add a new (empty) class module to your project. Then use the Properties window to give the class module a more descriptive name (such as clsChart ). Renaming the class module isn't necessary, but it's a good practice.

DECLARE A PUBLIC CHART OBJECT

The next step is to declare a Public variable that will represent the chart. The variable should be of type Chart , and it must be declared in the class module by using the WithEvents keyword. If you omit the WithEvents keyword, the object will not respond to events. Following is an example of such a declaration:

 Public WithEvents clsChart As Chart 

CONNECT THE DECLARED OBJECT WITH YOUR CHART

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 clsChart (or whatever your class module is named). This should be a module-level object variable, declared in a regular VBA module (not in the class module). Here's an example:

 Dim MyChart As New clsChart 

Then you must write code to associate the clsChart object with a particular chart. The statement below accomplishes this.

 Set MyChart.clsChart = ActiveSheet.ChartObjects(1).Chart 

After the preceding statement is executed, the clsChart object in the class module points to the first embedded chart on the active sheet. Consequently, the event handler procedures in the class module will execute when the events occur.

WRITE EVENT HANDLER PROCEDURES FOR THE CHART CLASS

In this section, I describe how to write event handler procedures in the class module. Recall that the class module must contain a declaration such as the following:

 Public WithEvents clsChart As Chart 

After this new object has been declared with the WithEvents keyword, it appears in the Object drop-down list box in the class module. When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down box on the right.

The following example is a simple event handler procedure that is executed when the embedded chart is activated. This procedure simply pops up a message box that displays the name of the Chart object's parent (which is a ChartObject object).

 Private Sub clsChart_Activate()     MsgBox clsChart.Parent.Name & " was activated!" End Sub 

CD-ROM  

The companion CD-ROM contains a workbook that demonstrates the concepts that I describe in this section. The file is events “ embedded chart.xlsm .

Example: Using Chart events with an embedded chart

The example in this section provides a practical demonstration of the information presented in the previous section. The example shown in Figure 18-16 consists of an embedded chart that functions as a clickable image map. When chart events are enabled, clicking one of the chart columns activates a worksheet that shows detailed data for the region.

Figure 18-16: This chart serves as a clickable image map.

The workbook is set up with four worksheets. The sheet named Main contains the embedded chart. The other sheets are named North, South, and West. Formulas in B1:B4 sum the data in the respective sheets, and this summary data is plotted in the chart. Clicking a column in the chart triggers an event, and the event handler procedure activates the appropriate sheet so that the user can view the details for the desired region.

The workbook contains both a class module named EmbChartClass and a normal VBA module named Module1 . For demonstration purposes, the Main worksheet also contains a check box control (for the Forms group ). Clicking the check box executes the CheckBox1_ Click procedure, which turns event monitoring on and off:

In addition, each of the other worksheets contains a button that executes the ReturntoMain macro that reactivates the Main sheet.

The complete listing of Module1 follows:

 Dim SummaryChart As New EmbChartClass Sub CheckBox1_Click()     If Worksheets("Main").CheckBoxes("Check Box 1") = xlOn Then         'Enable chart events         Range("A1").Select         Set SummaryChart.myChartClass = _           Worksheets(1).ChartObjects(1).Chart     Else         'Disable chart events         Set SummaryChart.myChartClass = Nothing         Range("A1").Select     End If End Sub Sub ReturnToMain() '   Called by worksheet button     Sheets("Main").Activate End Sub 

The first instruction declares a new object variable SummaryChart to be of type EmbChartClass - which, as you recall, is the name of the class module. When the user clicks the Enable Chart Events button, the embedded chart is assigned to the SummaryChart object, which, in effect, enables the events for the chart. The contents of the class module for EmbChartClass follow:

 Public WithEvents myChartClass As Chart Private Sub myChartClass_MouseDown(ByVal Button As Long, _   ByVal Shift As Long, ByVal  As Long, ByVal Y As Long)     Dim IDnum As Long     Dim a As Long, b As Long '   The next statement returns values for '   IDnum, a, and b     myChartClass.GetChartElement X, Y, IDnum, a, b '   Was a series clicked?     If IDnum = xlSeries Then         Select Case b             Case 1                 Sheets("North").Activate             Case 2                 Sheets("South").Activate             Case 3                 Sheets("West").Activate         End Select     End If     Range("A1").Select End Sub 

Clicking the chart generates a MouseDown event, which executes the myChartClass_ MouseDown procedure. This procedure uses the GetChartElement method to determine what element of the chart was clicked. The GetChartElement method returns information about the chart element at specified — and Y coordinates (information that is available via the arguments for the myChartClass_MouseDown procedure).

CD-ROM  

This workbook, named  chart image map.xlsm , is available on the companion CD-ROM.