Microsoft Office Tutorials and References
In Depth Information
Understanding Chart Events
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!”
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-14 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.
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 (from the Forms group). Clicking the check box executes the CheckBox1_Click
procedure, which turns event monitoring on and off: