Controls on Charts

Figure 10-4 shows a chart that contains a button to remove or add the profit series from the chart, which is based on the Profit Planner figures of the Profit sheet. The control is a Forms toolbar Button object belonging to the Buttons collection (remember, ActiveX controls cannot be used in charts).

Figure 10-4

The code assigned to the OnAction property of the Button object is as follows:

Sub Button1_Click() With ActiveChart

If .SeriesCollection.Count = 3 Then

.SeriesCollection(1).Delete Else

With .SeriesCollection.NewSeries .Name = Sheet1.Range("A13") .Values = Sheet1.Range("B13:M13") .XValues = Sheet1.Range("B12:M12") .PlotOrder = 1 End With End If End With End Sub

If the SeriesCollection.Count property is 3, the first series is deleted. Otherwise, a new series is added and the appropriate ranges are assigned to it to show the profit after tax figures. The new series is added as the last series, which would plot behind the existing series, so the PlotOrder property of the new series is set to 1 to place it in front of the others.

0 0

Post a comment