Formatting

I’ve posted several times recently (for example, here, here, and here) showing examples that use a listbox to allow the user to highlight a series in a chart. Then I realized that the listbox requires an additional element which is not really integral to the chart. So I thought, why can’t I use a list that’s built right into the chart: the Chart Legend.

In general it’s better to label the actual series than to force the user to move his eyes back and forth between the legend and the data. However, the technique that highlights one series among many already is likely to have too many series piled up on top of each other to use data labels, so the legend is actually one step better than a listbox.

So how can the legend be used to format series in the chart? Manually, of course, you can select the legend, then select and double click on the legend key, the small symbol next to the legend entry text, to open the Format dialog for that series. (In Excel 2007 you select the legend, then the legend entry label, then right click on the label and choose Format Series.) This is a tedious process, though, and you need to highlight one series and unhighlight the previously highlighted series.

The answer is to use Chart Events. This means programming the chart so it responds to events like activating the chart or clicking on or moving over a chart element. This isn’t too hard to do, though sometimes it takes a while to get your head around the concept and the mechanics.

This technique works on a chart sheet, not an embedded chart (Chart Event Class Module to Highlight a Series describes a more general approach that works for any chart). This specific code is for a line chart, but it can be adapted to any other chart type. First, activate the chart sheet, right click on the sheet tab, and select View Code. This activates the Visual Basic Editor (VBE) and opens the code module associated with the chart sheet. Code placed in this code module is applicable only to that specific chart sheet.

Copy the following VBA code, and paste it into the code module.

Option Explicit

Const iCOLOR_HIGHLIGHT As Long = 5  ' BLUE
Const iCOLOR_BLAND As Long = 15  ' 25% GRAY

Private miSeries As Long

Private Sub Chart_Activate()
  ResetSeries
End Sub

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, _
                            ByVal x As Long, ByVal y As Long)
  HighlightSeries x, y
End Sub

Private Sub Chart_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

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

  If ElementID = xlLegendEntry Or ElementID = xlLegendKey Then

    If miSeries <> Arg1 Then

      ResetSeries

      With Me.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 Me.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 was written in Excel 2003, but it should work in all versions between 97 and 2007. I specifically tested 2007 and it works as would be expected.

The constants merely define the color indexes for the highlighted and unhighlighted series. They are placed at the top of the module to make it easy to find and edit them.

There are two general sub procedures in this module. ResetSeries formats all series with the unhighlighted color. HighlightSeries accepts two arguments from the calling sub, x and y, which are the coordinates of the mouse when HighlightSeries is called; the chart element at this x-y mouse position is determined, and if it is a legend key or legend entry, the corresponding series and legend entry are highlighted and the others are unhighlighted.

The module contains three event procedures. Chart_Activate is run whenever the chart is activated, and it simply calls the ResetSeries procedure. Chart_MouseDown and Chart_MouseMove run when the mouse is clicked or moved, and both simply call the HighlightSeries procedure. Each passes parameters into the procedure: Button refers to the mouse button which was pressed; Shift indicates whether Shift, Ctrl, or Alt was simultaneously pressed; and x and y are the cursor position coordinates, in the mouse coordinate system, which is points from the top left corner of the chart, and therefore completely independent of the scales on the chart axes. Fortunately you need not worry about the units on x and y, because these are passed directly through to the HighlightSeries procedure, where they are consumed without modification by the GetChartElement function.

The MouseDown procedure is actually unnecessary, as you have to mouse over a chart element before you can click on it. I used it for my testing, and simply left it in place. If moving the mouse is too little provocation for you to change the highlighting, you can comment out the MouseMove event procedure and rely on clicking on the legend for your highlighting.

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