How do I return the "Series" and "Point" names of a Stacked Column chart in Excel using VBA

Question

I am exploring VBA in excel and would like to know how to get a hold of the Series and Point names from my stacked columns chart. If you have a stacked column, you can hover over any column and excel will show you a little text box with 'Series "< series-name>" Point "< point-name>" Value: ##'.

I would like to be able to either click or hover over any column in the chart and execute VBA code to find the series and point names. The most challenging task for me is to trigger the VBA script by a hover or mouse click and identify the active part within the active chart.

Any help is greatly appreciated!

Answer 1

It's not a task with an easy answer, as you can't trigger a macro to run when clicking on plot elements.

VB is usually set up to create charts, rather than read them.

Code such as:

Sub macro()
 If TypeName(Selection) = "Series" Then
 Set ch = ActiveChart.SeriesCollection
 For I = 1 To ch.Count
 If ch(I).Name = Selection.Name Then MsgBox "Series is " & Selection.Name
 Next I
 End If
End Sub

from http://www.vbaexpress.com/forum/showthread.php?43451-Chart-Series-Selected-Index will give you the selected series name. You would need to set up a hot key or button to run it. I haven't seen similar code to individual points.

But are you sure you need to accomplish this task with VB?

If I hover my mouse over a point in Excel 2010, the popup appears just as you want: screenhot

• Josh Jobin: thank you for your answer. I still would like to retrieve the names because I would like to execute different scripts depending on which column the user selects. And like you mentioned, the pop up does appear on hover over, I was hoping that the pop up could be intercepted somehow.

Answer 2

As for making your vba run: You create a chart_mousemove event actually in the chart itself (open the chart page in the macro/vba editor (alt-F11) once you're in the chart) and then it will run each time you move the mouse over the chart. It's like using events in windows forms, if you've ever done that.

Please see this page for more details http://www.databison.com/interactive-chart-in-vba-using-mouse-move-event/