The example in this section provides a practical demonstration of the information presented in the previous section. The example shown in Figure 18-13 consists of an embedded chart that functions as a clickable image map. 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 Modulel. For demonstration purposes, the Main worksheet also contains two buttons: One executes a procedure named EnableChartEvents; the other executes a procedure named DisableChartEvents. (Both are located in Modulel.) In addition, each of the other worksheets contains a button that executes the ReturntoMain macro that reactivates the Main sheet. The complete listing of Modulel follows:
Sub EnableChartEvents() ' Called by worksheet button Range("A1").Select Set SummaryChart.myChartClass = _ Worksheets(1).ChartObjects(1).Chart End Sub
Sub DisableChartEvents() ' Called by worksheet button
Set SummaryChart.myChartClass = Nothing Range("A1").Select 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. Listing 18-4 shows the class module for EmbChartClass.
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 X and Y coordinates (information that is available via the arguments for the myChartClass_ MouseDown procedure).
Listing 18-4: Reacting to Which Column Has Been Clicked
Public WithEvents myChartClass As Chart
Private Sub myChartClass_MouseDown(ByVal Button As Long, _ ByVal Shift As Long, ByVal X 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 l
Sheets("North").Activate Case 2
Sheets("South").Activate Case 3
Sheets("West").Activate End Select End If
Range("A1").Select End Sub
This workbook is available on the companion CD-ROM.
Was this article helpful?