VBA: How to update a trendline label in excel

Question

sometimes it happens that the trendline label in excel isn't updated when I changed the graph-data. Therefore I want to update via VBA. I want to do it for all existing trendlines in all sheets and charts.

My code until now doesn't work. You will find the error in the comment.

    Sub Auto_Open()
        Debug.Print "Start"
        Dim oChart As ChartObject, nSheet As Integer, nChart As Integer
        nSheet = 1
        Do While nSheet <= Sheets.Count
            nChart = 1
            Do While nChart <= Sheets(nSheet).ChartObjects.Count
                nSeriesCollection = 1
                'Debug.Print Sheets(nSheet).ChartObjects(nChart).SeriesCollection.Count
                'Error in next line
                Do While nSeriesCollection <= Sheets(nSheet).ChartObjects(nChart).SeriesCollection.Count
                    Debug.Print "nSheet: " & nSheet & " nChart: " & nChart
                    Set oChart = Sheets(nSheet).ChartObjects(nChart)
                    oChart.Activate
                    'Next line has to changed too
                    ActiveChart.SeriesCollection(1).Trendlines(1).Select
                    With Selection
                        .DisplayRSquared = False
                        .DisplayEquation = False
                        .DisplayRSquared = True
                        .DisplayEquation = True
                    End With            
                    nSeriesColletion = nSeriesColletion + 1
                Loop
                nChart = nChart + 1
            Loop
            nSheet = nSheet + 1
        Loop
    End Sub 

########################################################################

There is a run-time error '438'. Object doesn't support this property or method in the line with the error comment.

How to mark this question as answered? – kame Mar 08 '13 at 13:28 — kame, Mar 08 '13 at 13:28

score 0 · Answer 1 · answered Mar 07 '13 at 18:49

0

There is error in

ActiveChart.SeriesCollection(1).Trendlines(1).Select

Change it to:

ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select

answered Mar 07 '13 at 18:49

Travis G

1,592
1
13
18

Hi Lance, please see edit 2. There is a run-time error '438'. Object doesn't support this property or method in the line with the error comment. – kame Mar 08 '13 at 08:15

score 0 · Accepted Answer · answered Mar 12 '13 at 19:46

Here is the solution: :)

Sub Auto_Open()
    Debug.Print "###########################################"
    Debug.Print "Start"
    Dim oChart As ChartObject, nSheet As Integer, nChart As Integer
    nSheet = 1
    Do While nSheet <= Sheets.Count
        Debug.Print "Sheet: " & nSheet
        nChart = 1
        Do While nChart <= Sheets(nSheet).ChartObjects.Count
            Debug.Print " ChartObjects: " & nChart
            nSeriesCollection = 1
            Do While nSeriesCollection <= Sheets(nSheet).ChartObjects(nChart).Chart.SeriesCollection.Count
                Debug.Print "  SeriesCollection: " & nSeriesCollection

                Sheets(nSheet).ChartObjects(nChart).Chart.SeriesCollection(nSeriesCollection).Trendlines(1).DisplayEquation = True

                nSeriesCollection = nSeriesCollection + 1
            Loop
            nChart = nChart + 1
        Loop
        nSheet = nSheet + 1
    Loop
End Sub

score 0 · Answer 3 · answered Jun 29 '22 at 13:57

I'm waaay late to the game, but for posterity's sake...

I think the error can be avoided by using for each ... next construct rather than the do while ... loop option and eliminating the unnecessary Select(ellipses is other misc code):

...
For Each oSheet In Sheets
    iSheet = iSheet + 1 'if an indexing is needed'
...
    For Each oChart In oSheet.Charts
        iChart = iChart + 1 'if an indexing is needed'
...
        For Each oSeries In oChart.SeriesCollection
            iSeries = iSeries + 1 'if an indexing is needed'
            For Each oTrend In oSeries.Trendlines
                With oTrend
                    .DisplayEquation = False
                    .DisplayRSquared = False
                    'the next statement often assures eq is updated
                    'unsure if there is a more reliable solution
                    DoEvents
                    .DisplayEquation = True
                    .DisplayRSquared = True
                End With
            Next oTrend
...
        Next oSeries
...
    Next oChart
...
Next oSheet

VBA: How to update a trendline label in excel

3 Answers3