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.
Andreas Lipphardt says
Jon,
Its amazing what you can do with the Excel chart programing model.
The downside of this approach is that it requires you do code some VBA and only works with chart sheets ( I know the chart events also work in theory for embedded charts, but you have to activate the chart, in order to make Excel handle your events, which is one click you want to avoid)
I tried to create a code-free version on using ParamLinks on
http://blog.xlcubed.com/hyperlink-legends-to-highlight-a-series/
Andreas
Andreas Lipphardt says
>>but you have to activate the chart, in order to make Excel handle your events,
Is there a way to workaround this?
Thanks,
Andreas
Jon Peltier says
Andreas –
There are two senses in which you have to “activate” the chart. In one sense you have to make the chart the active chart. I don’t know how to make the chart respond to events without it being active. I’m also not sure this “extra click” is really a problem.
In the other sense you have to assign the chart to the code that makes the chart respond to events. You can make this automatic, using more code of course that instructs Excel to activate any chart in the active sheet whenever a sheet is activated. The technique works fine, and it is described in my article Chart Events in Microsoft Excel.
Andreas Lipphardt says
Jon,
>>not sure this “extra click” is really a problem
Not having this extra click in a dashboard would make the whole
selection, navigation user experience much smoother.
>>I don’t know how to make the chart respond to events without it being active
I fear then the only way to do this is to set a Low-level Windows API hook,
and to handle the Windows messages and activate the chart when the mouse is over the charts area.
Andreas
Jon Peltier says
Andreas –
>>Not having this extra click in a dashboard would make the whole
selection, navigation user experience much smoother.
Very true. I usually envision this kind of interactive activity in a more intensive view than a dashboard, but it could also be part of a dashboard. I wonder if there are mouseover events of other objects (ActiveX controls, etc.) that could be brought to bear.
>>…set a Low-level Windows API hook, and to handle the Windows messages…
Oh, I hope it’s not that scary.
Ajay says
Jon,
You may want to take a look at this graphic that appeared in the NY Times a few days back. (How This Bear Market Compares). It uses the pretty much same idea of highlighting a series as you’ve postulated here.
I borrowed your code (and made a few minor modifications in the process) and took a shot at replicating it:
How This Bear Market Compares – NY Times
Your approach does provide an excellent alternative to charting multiple series in a line graph, especially where data points overlap, so that the final result is ‘user-friendly’.
Regards,
Ajay
Jon Peltier says
Ajay –
Nice job.
jeff weir says
That’s nice work, Ajay. I’d suggest a slight change…do away with the text box saying what market the user is looking at on 2 counts:
1) It’s really distracting when you’re mousing over lines before you actually select anything. An empty box appears, and moves around even when you’re mousing over empty space on the graph. Maybe this is perculiar to Excel 2007. But its really distracting
2) It doesn’t add any info to what appears on the graph and what appears in the series ‘tooltip’ after you mouse over a series. eg mouse over the NOV series and you get a tooltip saying “Series “NOV 1973”. If you change your series name to “NOV 1973 Bear market: here’s what took place” then that’s what would appear in the tool tip.
Rajesh says
Hello,
I’m stuck in figuring out how to do the below. Would any of you experts be able to help me please?
I’ve got 2 worksheets “GRAPHS_1) & “GRAPHS_2”. Each of these sheets have 20 plus bar graphs. The bar graph is a representation of 4 entities A,B,C & D over the months of a year. When I click on any series in any of the bar graphs, I’d like to capture the date of the bar I clicked. That is, “Series”, “Point” & “Value” entities & do something.
I’ve googled a lot but have been unable to figure this out. Please can someone help?