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.
The example zip file is a dead link. Thanks!
Error 404 – Page not found!
Thanks for this Jon, however I’m having problems with the zipped file…or is it just me?
Jon Peltier says
Sorry folks. The link to the downloadble zip file has been corrected.
This is a great visual tool! Thanks Jon.
Michael Pierce says
Thank you for this example; looks like I’m going to learn a few things from this.
One suggestion: It would be good to capture the chart’s existing formatting (series colors, etc.) when the chart is activated and restore that formatting when the chart is deactivated.
Jon Peltier says
So the chart has “normal formatting”, then when it’s activated, go to the one highlighted series and the rest unhighlighted?
Is there a way to turn off Excel’s standard behavior of tip ? (tiny yellow box …)
Could the MouseOver event be a solution for a much cleaner substitute ?
Thanks in advance for your help.
Jon Peltier says
This is possible, but requires some code.
This was covered in Better Chart Tooltips with Microsoft Excel 2010 in the Clearly and Simply blog.
Thanks a lot for your answer.
In addition to the link you have indicated, I just found an article of yours, dealing with Chart Events, which is extremely useful :
brian blakeley says
This is a brilliant piece of coding! I like to do most of my work in Excel as i need to be able to do other things with my data so PowerBi is not as useful (even though easier to do what i am trying to do in Excel).
I was wondering, is there a way to get the name of the chart that has the data point selected? If i have multiple visuals it would be useful.
•i have a workaround where i use the series name as ‘chartname_series name’ so i can parse the name from there but would be great to not have to do that step.
Jon Peltier says
If you know the series, you can determine the chart. The chart is the parent of the series, and the chart object is the parent of the chart. So…
Set MyChart = MySeries.Parent
Set MyChartObject = MySeries.Parent.Parent
MyChartObjectName = MySeries.Parent.Parent.Name