Example Using Chart events with an embedded chart

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

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.

Vba Multiplication Table Programming
Figure 18-13: This chart serves as a clickable image map.

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:

Dim SummaryChart As New EmbChartClass

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?

+1 -4

Post a comment