In Chart Event to Highlight a Series, I showed how to use a chart legend plus chart events instead of a listbox, to select which series to highlight in a busy multi-series chart. That technique is useful, but it is not versatile: it is applicable only to a chart sheet that has the code in the chart sheet’s code module.
This can be remedied by using a class module. The chart sheet’s module is itself a class module, for the chart, which is an object of the class Chart. Objects of this class which are embedded in other sheets (as opposed to residing on their own chart sheets) do not support their own modules. But we can create a new class module which can be applied to any chart.
To create a class module, select the workbook’s VB Project in the Project Explorer of the VB Editor. Right click on any of the Project’s members and choose Insert > Class Module, or from the Insert menu, choose Class Module. Name this class module CHighlight. Paste the code below into the class module.
Option Explicit Public WithEvents cht As Chart Const iCOLOR_HIGHLIGHT As Long = 5 ' BLUE Const iCOLOR_BLAND As Long = 15 ' 25% GRAY Private miSeries As Long Private Sub cht_MouseDown(ByVal Button As Long, ByVal Shift As Long, _ ByVal x As Long, ByVal y As Long) HighlightSeries x, y End Sub Private Sub cht_MouseMove(ByVal Button As Long, ByVal Shift As Long, _ ByVal x As Long, ByVal y As Long) HighlightSeries x, y End Sub Private Sub HighlightSeries(ByVal x As Long, ByVal y As Long) Dim ElementID As Long Dim Arg1 As Long Dim Arg2 As Long cht.GetChartElement x, y, ElementID, Arg1, Arg2 If ElementID = xlLegendEntry Or ElementID = xlLegendKey Then If miSeries <> Arg1 Then ResetSeries With cht.Legend.LegendEntries(Arg1) With .Font .ColorIndex = iCOLOR_HIGHLIGHT .Background = xlTransparent .FontStyle = "Bold" End With With .LegendKey.Border .ColorIndex = iCOLOR_HIGHLIGHT .Weight = xlMedium .LineStyle = xlContinuous End With End With miSeries = Arg1 End If End If End Sub Private Sub ResetSeries() Dim lgnd As LegendEntry Application.ScreenUpdating = False For Each lgnd In cht.Legend.LegendEntries With lgnd.Font .ColorIndex = xlAutomatic .Background = xlTransparent .FontStyle = "Regular" End With With lgnd.LegendKey.Border .ColorIndex = iCOLOR_BLAND .Weight = xlThin .LineStyle = xlContinuous End With Next Application.ScreenUpdating = True End Sub
This code is almost identical to the code in the chart sheet module in Chart Event to Highlight a Series. One difference is the public variable cht, which is declared WithEvents, which means it will respond to events, that is, actions applied to the object.
We still need to instantiate the class and define the variable cht. If necessary insert a module the same way you inserted a class module above. Paste the following code into the module.
Option Explicit Global gclsHighlight As New CHighlight Sub ActivateChart() If Not ActiveChart Is Nothing Then Set gclsHighlight.cht = ActiveChart Else MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart" End If End Sub Sub DeactivateChart() Set gclsHighlight.cht = Nothing End Sub
Declaring the global class variable gclsHighlight with the New keyword instantiates the class.
To assign events to a chart sheet or an embedded chart, select the chart and run the ActivateChart procedure. This procedure sets the cht variable of the class equal to the active chart, thus defining the active chart as the chart which will respond to the events.
When a new chart is activated, the previous one is deactivated automatically. If you need to deactivate the chart, run the DeactivateChart procedure (no chart needs to be selected first).
You can download a simple workbook that illustrates this technique: ChartEventClassHighlightSeries.zip.