Chart Sheets

Chart events are automatically enabled with chart sheets. To catch events triggered by the user in a chart sheet, add code to an event procedure contained in the module associated with the chart sheet. The code window can be opened in the same manner as with a worksheet. Figure 9.10 shows the code window of a chart sheet selected from the project explorer. The active project displayed in Figure 9.10 is an Excel workbook containing several chart sheets.

Chart sheet component module

Chart sheets*

Adding code to an event procedure of a chart sheet.

Chart sheet component module

Chart sheets*

Adding code to an event procedure of a chart sheet.

Unfortunately, some of the events unique to the Chart object cannot be used with a chart sheet because there is no manner in which the user can trigger them. For example, the user cannot drag and drop a range of cells over the chart when the data is in another worksheet; however, the other chart events work as expected, and an example using the Select() event procedure of the Chart object is listed here.

Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long) If ElementID = xlSeries And Arg2 > 0 Then

ActiveChart.SeriesCollection(Arg1).Points(Arg2).ApplyDataLabels Type:=xlShowValue

End If End Sub

The Select() event procedure of the Chart object accepts three parameters: ElementID is a long integer that refers to the component object selected by the user (ChartArea, PlotArea, Series, and so on), and Arg1 and Arg2 are long integers that refer to specific components of the selected object; thus, the meaning of Arg1 and Arg2 depends on the object selected by the user. The definitions of Arg1 and Arg2 for some of the more common chart components are listed in Table 9.4.

Table 9.4 Argument Definitions for the Select()

The preceding Select() event procedure is triggered when the user selects a chart component. If that component is a single data point on the chart then Arg1 holds the index value of the selected Series object (representing a series of values) and Arg2 holds the index value of the selected Point object (representing the individual values in the series).

The purpose of the code entered in the Select() event procedure is to add a label to any point in a data series selected by the user. To accomplish this, the parameter ElementID is tested for equivalence to three (VBA-defined constant xlSeries, see online help for additional constants) because that's the value that represents a Series object. If the user has selected a single point in a data series, the selected point is labeled with its value by using the ApplyDataLabels() method of the Point object and setting the Type argument to the constant xlShowValue. In this example, Arg2 holds the value -1 if the entire series is selected and will not hold a meaningful value until the user selects an individual point from the data series. When the user does select an individual data point, the value of Arg2 is passed to the Points() method, which returns a Point object from the Points collection object. In this case, the Points() method returns the specific data point selected by the user.

Consider the chart shown in Figure 9.11 where two data series are plotted in a scatter chart.

Detecting a user selection with the Select() event of the Chart object.

Detecting a user selection with the Select() event of the Chart object.

-Data label

The chart is contained in a chart sheet and the Select() event procedure of the Chart object contains the previously listed code. If the user selects Series 1 with a single click of the mouse, the Select() event procedure is triggered but the parameters passed to the procedure are ElementID=3, Arg1=1, and Arg2=-1; so the conditional expression in the If/Then statement is false; therefore, no label is added to the chart. With Series 1 selected, the user then clicks on the 6th data point in Series 1. Again, the Select() event procedure is triggered, but this time the parameters passed to it are ElementID=3, Arg1=1, and Arg2=6. This time, the conditional in the If/Then statement is true and the label 54 is added to the chart.

Before writing the code for the Sel ect() event procedure, I recorded a macro while adding a Label to a charted point. This reminded me how to add the Label to individual data points using VBA.

To learn how to use the Select() event procedure of the Chart object, I added the statement Debug.Print ElementID; Arg1; Arg2 to the procedure and watched the Immediate window while I clicked on various components of the Chart object.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment