class module

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 assign either or both commands to a Button or Shape, to an ActiveX Control, or to a Toolbar or Menu, run them from the Tools menu > Macro > Macros, or use the Alt+F8 shortcut function key.

You can download a simple workbook that illustrates this technique: ChartEventClassHighlightSeries.zip.