VBA

I showed in Easier Interactive Multiple Line Chart and in Gas Prices – Interactive Time Series how to use a listbox to highlight a particular series. This keeps its alignment, but also won’t work on a chart sheet.

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 multi-series chart sheet. In Chart Event Class Module to Highlight a Series I described a more general approach that works for a chart sheet or an embedded chart. In this article I will show how to apply this class module approach to highlight a series as it is clicked or moused over.

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

' Click on a series
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

' Mouse over a series
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
  Dim iLegendEntry As Long

  cht.GetChartElement x, y, ElementID, Arg1, Arg2

  If ElementID = xlSeries Or ElementID = xlDataLabel Then

    If miSeries <> Arg1 Then

      ResetSeries

      With cht.SeriesCollection(Arg1)

        With .Border
          .ColorIndex = iCOLOR_HIGHLIGHT
          .Weight = xlMedium
          .LineStyle = xlContinuous
        End With

      End With

      If cht.HasLegend Then
        For iLegendEntry = 1 To cht.Legend.LegendEntries.Count
          If cht.Legend.LegendEntries(iLegendEntry).LegendKey.Border.ColorIndex = iCOLOR_HIGHLIGHT Then
            With cht.Legend.LegendEntries(iLegendEntry).Font
              .ColorIndex = iCOLOR_HIGHLIGHT
              .Background = xlTransparent
              .FontStyle = "Bold"
            End With
          End If
        Next
      End If

      miSeries = Arg1
    End If

  End If

End Sub

Private Sub ResetSeries()
  Dim lgnd As LegendEntry
  Dim srs As Series

  Application.ScreenUpdating = False

  For Each srs In cht.SeriesCollection
    With srs.Border
      .ColorIndex = iCOLOR_BLAND
      .Weight = xlThin
      .LineStyle = xlContinuous
    End With
  Next

  For Each lgnd In cht.Legend.LegendEntries
    With lgnd.Font
      .ColorIndex = xlAutomatic
      .Background = xlTransparent
      .FontStyle = "Regular"
    End With
  Next

  Application.ScreenUpdating = True

End Sub

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: HighlightSeriesWithClickOrMouseOver.zip.