Excel 2013 - How to check whether a chart series is selected

[Ehcacommence]

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

[mancubus]

check this out:
http://www.vbaexpress.com/forum/show...Selected-Index

[Ehcacommence]

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]

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]

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...

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
I will soon be back with a new thread asking for help about mouse hovering events though...

Thanks again, great help !

Cheers !

[JonPeltier]

When you get around to mouse events, check out my tutorial Chart Events in Microsoft Excel