Enabling events for an embedded chart

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

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.


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.


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


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.


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

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 (see Figure 18-12).

Figure 18-12: The Procedure list displays valid events for the new object.

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 on the cd on the cd

The companion CD-ROM contains a workbook that demonstrates the concepts that I describe in this section.

Was this article helpful?

+4 0

Post a comment