View Full Version : [SOLVED:] Excel 2013 - How to check whether a chart series is selected
Ehcacommence
03-16-2018, 01:02 PM
Hi !
I would need to ask VBA to check whether a series in a chart is selected.
I have tried things like :
If ActiveChart.FullSeriesCollection(e).Select = True Then
Or :
If ActiveChart.FullSeriesCollection(e) is Selected Then
And, well, this is it really ! I guess the way I try to do that is all wrong...
By the way, to provide some context as to why I want to do that :
e is a variable that will loop through the different series index and legend entries index of a chart.
If VBA finds a series (that is, a bar in the chart) that is selected, then it will apply a glow to the legend entry is linked to.
Thanks for reading, and possibly, for helping :hi:
mancubus
03-16-2018, 03:15 PM
check this out:
http://www.vbaexpress.com/forum/showthread.php?43451-Chart-Series-Selected-Index
Ehcacommence
03-17-2018, 09:40 AM
Thanks for your answer, but I don't think that it's what I'm looking for.
Well, I am afraid I do not want the code to tell me "that is the index number of the selected bar".
I need to tell VBA "If a bar is selected then apply glow on the legend entry the bar is linked to, if not then apply a standard formatting to its legend entry and go check and do the same thing for the next bar until all the bars of the chart have been checked".
The bit I really miss is how to ask vba whether a chart bar is selected or not, as my two code attempts might show.
JonPeltier
03-18-2018, 07:19 PM
By bar, do you mean a series or an individual point?
TypeName(Selection) tells you what kind of object is selected, e.g., "Range", "Series", "Point", "Axis", "Legend".
By formatting a series, you also format the legend key, which is the little symbol next to the series name in the legend. The symbol and text together are the legend entry.
If TypeName(Selection) is "Series", then you could apply the glow to the Selection and it appears in the legend key; if it's "Point" then apply the glow to the Selection.Parent. If you want to apply a glow to the whole legend entry, it's a bit more complicated, because in VBA the series isn't tied directly to the legend entry.
Ehcacommence
03-19-2018, 02:44 PM
Thanks both for your help !
Sorry, I hadn't realized I could use If TypeName(Selection) = "Series" ! I am fairly new to VBA, so, yeah... :banghead:
Here I am, it works pretty good so far :
Sub Legend_Entry_Text_Glow()
Application.ScreenUpdating = False
' Legend_Entry_Text_Glow Macro
' If a chart series is selected, applies standard format to legend entries then
' changes the selected bar's legend entry to black & applies glow effect
' Else applies standard format to legend entries
If TypeName(Selection) = "Series" Then
Set ch = ActiveChart.SeriesCollection
For i = 1 To ch.Count
If ch(i).Name = Selection.Name Then
For e = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.Legend.LegendEntries(e).Select
With Selection.Format.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText2
.ForeColor.Brightness = 0.8000000119
.Solid
End With
Selection.Format.TextFrame2.TextRange.Font.Glow.Radius = 0
Next e
ActiveChart.Legend.LegendEntries(i).Select
With Selection.Format.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.Solid
End With
With Selection.Format.TextFrame2.TextRange.Font.Glow
.Color.RGB = RGB(146, 208, 80)
.Radius = 10
End With
With Selection.Format.TextFrame2.TextRange.Font.Glow
.Color.RGB = RGB(146, 208, 80)
.Radius = 60
End With
Application.ScreenUpdating = True
Exit Sub
End If
Next i
Application.ScreenUpdating = True
Exit Sub
End If
ActiveSheet.ChartObjects("Chart 1").Activate
For e = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.Legend.LegendEntries(e).Select
With Selection.Format.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText2
.ForeColor.Brightness = 0.8000000119
.Solid
End With
Selection.Format.TextFrame2.TextRange.Font.Glow.Radius = 0
Next e
Application.ScreenUpdating = True
End Sub
This is all very sweet.
The next step is to make that happen on the Event of a Mouse Hover on a series, instead of running a macro once a series is selected...
Hum. I didn't work at all on it, so far. But I will !
Any idea on that matter would be much appreciated, but I think I now have to close this topic and mark it as solved :yes
I will soon be back with a new thread asking for help about mouse hovering events though... :devil2:
Thanks again, great help !
Cheers !
JonPeltier
03-19-2018, 07:57 PM
When you get around to mouse events, check out my tutorial Chart Events in Microsoft Excel (https://peltiertech.com/chart-events-microsoft-excel/)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.