Selecting a Chart series by name and then formatting

[iepritchard]

Hi All,

I have a chart on a chart sheet ("Chart2"), and the data on a separate sheet ("Sheet3"). I have multiple series plotted on the chart, but I also will plot All the data and name the series "All" (It will also be the last series). For this series I would like to not show the data markers but I do wish to show a trend line for the series. My main problem at the moment is that I can't select the "All" series on which to perform the formatting..?? The code I have at the moment is...

Sub Graph_Format()

Dim serColl As SeriesCollection
Set serColl = Charts(1).SeriesCollection

serColl(Count).Select

With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
Selection.Trendlines.Add(Type:=xlLogarithmic, Forward _
:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select
Selection.Trendlines(1).DataLabel.Select
Selection.Left = 442
Selection.Top = 194

End Sub


Any suggestions..??!

Thanks,
Ian

 

[Andrew Poulsom]

Is it?

    Dim ser As Series
    With Charts(1)
        Set ser = .SeriesCollection(.SeriesCollection.Count)
    End With
    ser.Select

 

[iepritchard]

I'm afraid not.. It highlighted ser.select with the error 'Select Method of series class failed'...?

 

[Andrew Poulsom]

It worked when I tried it.

What code do you get if you record a macro while selecting the series manually?

 

[iepritchard]

Recording I get..

Sheets("Chart2").Select
ActiveChart.SeriesCollection(14).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(14).Trendlines.Add(Type:=xlLogarithmic, Forward _
:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select
End Sub

where series 14 is the last series.

 

[Andrew Poulsom]

So this should work:

    Sheets("Chart2").Select
    With ActiveChart
        .SeriesCollection(.SeriesCollection.Count).Select
    End With