Using Events with Charts

One frustration of working with charts is that as the shape of the data changes, Excel often changes aspects of the chart to fit the data. This can cause perfect formatting to go astray.

Luckily, some events associated with charts can be trapped. (To learn more about event programming, read Chapter 8, "Event Programming.") Code for ChartSheet events should be placed on the worksheet module for the sheet that contains the chart. If you need to do event-based programming for embedded charts, you need to write a class module to work when one of the chart events is triggered. Some very useful events for charts include the following:

■ SeriesChange—Detects whenever a change is made to a series

■ Calculate—Detects whenever the values in a chart change

■ Activate —Detects that the chart has been activated

■ Deactivate —Detects that the chart has been deactivated

The following macro runs whenever the chart is recalculated (for example, when you change the data series). It makes the series line on the sample XY chart red if the number of data points is greater than 5 and blue if less than or equal to 5:

Private Sub Chart_Calculate() Dim Ser As Series Set Ser = Me.SeriesCollection(l) If Ser.Points.Count > 5 Then Ser.Border.Colorlndex = 3


Ser.Border.Colorlndex = 5 End If End Sub

0 0

Post a comment