Embedded Chart Events

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

If you want to trap events for a chart embedded in a worksheet, you use a process similar to the process for trapping application events. First, insert a new class module into your project, or you could use the same class module that you used for the application events. You place the following declaration at the top of the class module:

Public WithEvents cht As Chart

Set up the same BeforeDoubleClick event procedure used in Chapter 10. The class module should be as follows:

Public WithEvents cht As Chart

Private Sub cht_BeforeDoubleClick(ByVal ElementID As Long, _

ByVal Argl As Long, ByVal Arg2 As Long, Cancel As Boolean) Dim se As Series

'Determine what part of chart was double clicked Select Case ElementID

'If legend, remove it Case xlLegend

ActiveChart.HasLegend = False Cancel = True

'If chart area, display legend Case xlChartArea

ActiveChart.HasLegend = True Cancel = True

'If series, determine which series Case xlSeries

'Argl is the Series index

'Arg2 is the point index (-1 if the entire series is selected) Set se = ActiveChart.SeriesCollection(Argl) If Arg2 = -1 Then

'Whole series selected With se.Border

If .ColorIndex = xlColorIndexAutomatic Then

.ColorIndex = 1 Else

.ColorIndex = (.ColorIndex Mod 56) + 1 End If End With


'Data point selected With se.Points(Arg2)

.HasDataLabel = Not .HasDataLabel End With

End If

'Cancel double click Cancel = True

End Select

End Sub

This code allows you to double-click the chart legend to make it disappear, or double-click in the chart area to make it reappear. If you double-click a series line, it changes color. If you select a point in a series by clicking it, and then double-click it, it will toggle the data label on and off for that point.

Say your chart is contained in a ChartObject that is the only ChartObject in a worksheet called Mangoes, as shown in Figure 16-5, and you have named your class module CChartEvents. In your standard module, you enter the following:

Public chtMangoes As CChartEvents

Sub InitializeChartEvents()

'Create instance of CChartEvents Set chtMangoes = New CChartEvents

'Assign reference to chart to cht Set chtMangoes.cht = ThisWorkbook.




End Sub

Frustration Worksheets For Kids
Figure 16-5

After executing InitializeChartEvents, you can double-click the series, points, and legend to run the BeforeDoubleClick event procedure.

Was this article helpful?

0 0


Post a comment