Check if Trendline Exists in VBA

Question

I have a sheet with a chart in it. I need to insert a button to TOGGLE the Trendline On/Off. So first, I need to check whether a trendline exists or not. Sounds simple, but is driving me NUTS!!!

Here is the code that I use to create the trednline and format it:

Sub Trending() 

Sheets("Sheet1").ChartObjects(1).Activate
    ActiveChart.SeriesCollection(1).Trendlines.Add
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    With Selection
        .Type = xlPolynomial
        .Order = 3
        .Border.ColorIndex = 3
        .Border.Weight = xlMedium

    End With

End Sub

To check for the existence of a trendline, I tried:

If Sheets("Sheet 1").ChartObjects(1).SeriesCollections(1).Trendlines(1).Count = 1 Then
    [Statement]
End If

But the test fails.

What am I doing wrong? What is a non-dirty way to do it?

Thanks, Al

Answer 1

There are three things wrong with your If statement. Had you broken down your statement in smaller bits and tested each of them separately, you would have found that:

1. It's Sheet1, not Sheet 1. No space. This is what causes your "Subscript is out of range" error.
2. The SeriesCollection property applies to Chart objects, not ChartObject object. Yes, I know this stupid Excel terminology is confusing. Anyhoo, you need ChartObjects(1).Chart.SeriesCollection(1), not ChartObjects(1).SeriesCollection(1).
3. Trendlines(1) returns a Trendline object, which is not Countable. You want to count the itemts in the trendlines collection, i.e. Trendlines.Count, not Trendlines(1).Count.

To summarise, this is how to write it:

If Sheets("Sheet1").ChartObjects(1).Chart _
    .SeriesCollection(1).Trendlines.Count > 1 Then
    MsgBox "there is exactly one trendline"
End If

Note: this will only work if the trendline count is exactly one. Consider replacing = with >= if there may be more than one.