Change a graph trendline order using VBA?

[Deladier]

Hello.

I have a graph and using VBA I can change the order of that graph trend line but I have problems trying to change to polynomial type to linear type, apparently because of the order.
I believe, in the whole code, 'Order = tlgrade' code part shouldn't appear for a linear graph case. How can 'Order = tlgrade' dissapear (for linear trendline case) or appear (for polynomial trendline case) in the code structure?

Here is my code:


Sub Macro1()

Dim tlgrade As Integer, tltype As String

tlgrade = Range("B10").Value

ActiveSheet.ChartObjects("Graph 1").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select


If tlgrade = 1 Then tltype = xlLinear
If tlgrade > 1 Then tltype = xlPolynomial



With Selection
.Type = tltype
.Order = tlgrade 'I think this part conditionally should appear or dissapear as the case
.Forward = 0
.Backward = 0
.InterceptIsAuto = True
.DisplayEquation = True
.DisplayRSquared = True
.NameIsAuto = True
End With

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B10" Then
Macro1
End If
End Sub


How can I solve it?

Thanks in advance. Best regards

 

[RoryA]

Perhaps simply:

If tlGrade > 1 then .Order = tlgrade

 

[Deladier]

I've got an error, why? (BTW, I'm using Excel 2003):


Sub Macro1()

Dim tlgrade As Integer, tltype As String

tlgrade = Range("B10").Value

ActiveSheet.ChartObjects("Gráfico 1").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select


If tlgrade = 1 Then tltype = xlLinear
If tlgrade > 1 Then tltype = xlPolynomial



With Selection
.Type = tltype
If tlgrade > 1 Then .Order = tlgrade
.Backward = 0
.InterceptIsAuto = True
.DisplayEquation = True
.DisplayRSquared = True
.NameIsAuto = True
End With

End Sub

 

[RoryA]

What is the error, and on which line?

 

[Deladier]

When tlgrade=1 an emergent window appears and it shows:

'1004' error on runtime
The Type property of the Trendline class is not assignable

and the error indication in code is located at '.Type = tltype' line.

When I move the mouse over that line I get: tltype = "-4132".

No errors when tlgrade>1.

 

[Deladier]

Eureka!, I found a little bit longer way to fix it, thanks anyway. Here is the code:

Sub Macro1()

Dim tlgrade As Integer, tltype As String


tlgrade = Range("B10").Value

ActiveSheet.ChartObjects("Gráfico 1").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select


If tlgrade = 1 Then GoSub Lin
If tlgrade > 1 Then GoSub Pol


Lin:

With Selection
.Type = xlLinear
.Backward = 0
.InterceptIsAuto = True
.DisplayEquation = True
.DisplayRSquared = True
.NameIsAuto = True
End With
GoTo Fnsh


Pol:

With Selection
.Type = xlPolynomial
.Order = tlgrade
.Forward = 0
.Backward = 0
.InterceptIsAuto = True
.DisplayEquation = True
.DisplayRSquared = True
.NameIsAuto = True
End With

Fnsh:

End Sub