Excel VBA to change Polynomial trendline on graph

[JezzaH]

G'day everyone,

I'm just after a little bit of help with a feature that I am trying to put in to my spreadsheet.

I have a sheet with a table of data that is then graphed on a chart ("Chart 3"). What is of use to me is the polynomial trendline with this data. I want to be able to select a drop down menu in cell j14 to choose between a 4th order and a 5th order polynomial. I would prefer this than having to go into the format trendline menu.

Since VBA is not my strength, I resorted to ChatGPT to write me some code. I'm not sure if this is frowned upon or not! Regardless, the code didn't work.

I was hoping someone might be able to review the code and give me some hints on where it went wrong. The code is as follows

Sub AdjustTrendlineOrder()

    Dim ws As Worksheet
    Dim chrt As chart
    Dim trendlineOrder As Integer
    Dim cellValue As String

    ' Set the relevant worksheet
    Set ws = ThisWorkbook.Worksheets("Ref Transducers Uncert Calc") 'change "Sheet1" to the name of your worksheet

    ' Set the chart object
    Set chrt = ws.ChartObjects("Chart 3").chart

    ' Get the value from cell J14    cellValue = ws.Range("J14").Value

    ' Determine the trendline order based on cell value
    If cellValue = "4th Order Polynomial" Then
        trendlineOrder = 4
    ElseIf cellValue = "5th Order Polynomial" Then
        trendlineOrder = 5
    Else
        ' Exit if the value is neither 4th nor 5th order polynomial
        MsgBox "The value in J4 is not recognized.", vbExclamation
        Exit Sub
    End If

    ' Change the trendline order
    chrt.SeriesCollection(1).Trendlines(1).Type = xlPolynomial
    chrt.SeriesCollection(1).Trendlines(1).Order = trendlineOrder

End Sub

Thanks in advance!!

 

[duggie33]

I think the ChatGPT questions are frowned upon...

Without testing this, I would try changing "SeriesCollection" to "FullSeriesCollection".

Hope that helps,

Doug