Microsoft Office Tutorials and References
In Depth Information
Understanding Chart Events
This procedure displays a message box that contains a description of the selected item, plus the
values for Art1 and Arg2 . 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.
This isn’t 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