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.
Thom Mitchell says
Jon: When I use the file you provided in a zip file on the original posting, the whole chart “flashes” when the macro executes or the event occurs. It’s as if the whole chart object is being “refreshed” by the monitor. Is this Excel 2000, the essential nature of what the VBA is doing or is it something else entirely? (As you know, I have some charts that are pretty “busy,” so I’m trying to assess whether one or more of your series highlighting posts would be appropriate for me to “steal”!)
Thom Mitchell says
P.S. In contrast, Gas Prices Line.xls does *not* behave in the same way:
The entire chart does not flash or “flicker” when a different line of the listbox is selected. For this reason, I find the gas prices solution better if (a) the chart is embedded *and* (b) there aren’t “too many” series.
Jon Peltier says
Thom –
Which precise file was this? I ran through a few and didn’t see any noticeable flashing. I don’t see why 2000 through 2003 would have problems. 2007, well, ….
Thom Mitchell says
Here is the path to the zip file:
https://peltiertech.com/wp-content/img200808/ChartEventClassHighlightSeries.zip
Jon Peltier says
Thom – I had actually tried that one. I guess you could say that there’s a very slight flicker, but much less pronounced than it would be if the whole chart were being refreshed.
Colin says
I’m developing a marksbook (well upgrading an existing one) and one of the features I’ve added is the ability to produce dot frequency graphs (via the XY Scatter chart type) of a class so that the distribution of students can easily be seen. I’d like to add the ability to have individual dots display the name of their student when hovered over and had rather despaired of being able to do this until I found your post – which seemed ideal (although I’d obviously have to modify it slightly so that the data points were affected rather than the legend).
Unfortunately, when I tried your code (unmodified), nothing seemed to happen. I wondered if it was some conflict with my existing code so I downloaded your example chart and tried that… nothing. There simply seems to be no visible effect as I move the mouse over the chart legend or click on it. Well, apart from the default Excel behaviour of displaying a box saying “Legend”.
I honestly can’t see what I’m doing wrong. Does the technique no longer work for Excel 2010 perhaps? I’m happy to give you access to my code if you want to have a look.
Jon Peltier says
Colin –
Did you select the chart, then run the ActivateChart code? This makes the chart recognize events and run the event code.
Colin says
Thanks for replying so rapidly! I followed your instructions as precisely as I could – creating the Class module, naming it as specified, pasting your code into it. Then adding the ActivateChart() and DeactivateChart() routines to my current Graphs module and putting the ActivateChart command in my code immediately after the creation of the chart.
I’ve taken a series of snapshots of the code and pasted them together, along with an image of the sheet and chart they produce, and put them at the link below. It’s not complete since a lot of the code is obviously not relevant but I hope you can see what I’m basically doing. (Probably not in the most effective way since I’m not a professional programmer!)
Link: —- http://www.hphomeview.com/images/grphcode.jpg
What puzzles me in particular is that when it didn’t work I assumed that I had done something wrong and downloaded your zip file example to see it in action. However, this too did not seem to work. Hence my question earlier about whether it might be because I was using Excel 2010. To be precise – Office Prof Plus 2010 Vers 14.0.6129.5000 (32 bit) on a Win 7 64 bit machine.
Note: The original problem that I had was that I wanted the name of the student displayed when the user hovered over a dot instead of just its coordinates. Now I have, in a way, solved the problem by loading each point as a separate series, named for the student. See the code. This works but seems IMHO an incredibly clumsy way to do it. Your method would be far more elegant if I could get it to work and change the code so that rather than bolding the legend it brought up (say) a textbox with the student’s name, class & mark in it.
Any suggestions would be welcome!
Jon Peltier says
Colin –
What you want the chart event code to do is much different than what the code here actually does. What it does is highlight a series when the legend is clicked or moused over. You want a label to appear when a point is moused over. Different events, different outcome. I also don’t think you want to activate the chart event code in the routine that creates the chart, especially not so early in the routine. Make the user do that, for example with buttons.
The class module code needs to be adjusted so it responds to a point being moused over. To apply a label, give the point a data label (after removing all others), find the cell to the left of the point’s coordinates, and assign the contents of this cell to the point’s data label. You could also change the formatting of the marker to help the user see which point is highlighted.
You could insert a class module, name it CDataPointLabel, and give it this code:
Then in a main module:
Add a couple buttons, one linked to ActivateChart, the other to DeactivateChart, and you’re ready to go.
Jon Peltier says
Colin –
I’ve posted a sample workbook here:
https://peltiertech.com/images/2013-01/LabelDataPointOnMouseOver.xlsm
Colin says
Thank you immensely for having put so much time into this – I did indeed realise that what I wanted it to do was significantly different to what your code did and was assuming I’d have to do quite a bit of modification to bend it to my needs. I suspect that you’ve saved me a great deal of time!. I’ll have a play with what you’ve just posted. :-)
I must admit that I can’t quite see the advantage in requiring the user to click a button to activate the property of having the data labels appear though: I want that ability to be there from the moment the graph is made available at the end of the procedure so that they can routinely just mouse over a point to see the student’s name associated with it. ie as a native ability of the chart at all times.
BTW, I solved the problem of why the example file I downloaded (the ChartEventClassHighlightSeries.zip file) was seemingly not working – I just didn’t realise that I had to select the chart and use the Run Macro command to activate it – I’d assumed it was already good to go, so to speak, as soon as it was opened. Senior moment I guess. :-) Once I got it working it was highly impressive and I’m looking forward to seeing what I can do with your new code. Thanks again.
Colin says
Your example file is great! I can see the end of the tunnel for this part of the project now. :-)
May I suggest that you consider writing this labelling trick up as a separate posting? I cannot tell you how many forums I’ve gone through in the past few days and the number of posts that I’ve seen asking pretty much the same question as I had on how to create dynamic pop-up labels for points on scatter graphs. There are a number of utilities and addons that will label the points for you based on another column of data but no-one else (that I’ve found) has come up with such an elegant solution to doing it a) as labels that only appear on mouseover, and b) via code that doesn’t require that the chart already exist.
Jon Peltier says
Colin –
While I put together this little demo file, I was thinking it would make a nice post. Unfortunately, I’m not starved for ideas, but for time.
Jason Gross says
I have been trying to do this for a while so I am glad to see you have the examples in here. I have a dashboard with multiple pages and many 20+ charts so labels need to be hidden or they will overlap. I will be working on this as well but I had some additional recomendations on how this could be implemented.
To activate the chart you could put an active x control set to clear. The activex controls have a mouse over event. When you go over the active x control activate the chart and run “Start Labeling”
Also you should put in code to deactivate the standard chart tooltip that way you don’t have 2 labels. Wish me luck as I attempt this myself.
Jason
Anonymous says
Hi, how can i use the highlight series for protected charts?
Jon Peltier says
A chart has to be active in order to register events. A protected chart cannot be activated. Therefore, you cannot use chart events with a protected chart.