How can I Select xlPolynomial xlLinear separately

[herbertheien]

Hi,

I am sort of playing around with VBA. Currently I have set up two check boxes that are tied to macros that add in trend lines. The problem that I am having is that I want to uncheck the boxes and delete the proper trend lines. I realize that my code is a muck below, but someone would read through it and help me fix it that would be really nice. If there was a way to select an xlPolynomial, or and xlLinear Trend line, then I think I would be set.

I basically got all of my code online and pieced it together, and have been coding VBA now for about 3 days.






'----------------------------- Polynomial Trend Line ---------------------------------------
Sub trendline_poly()
On Error GoTo FileError
'Place this right before the line that may cause a file error
'Include File opening code here
On Error GoTo ErrorHandler 'Place this right after the line that may cause a file error to resume regular error handling
Dim myBox As CheckBox
Dim i As Integer
Dim j As Integer

Set Sh_1 = ActiveWorkbook.Sheets("Lists")
Set Sh_2 = ActiveWorkbook.Sheets("Utilization_by_Product_Only")
Set Sh_3 = ActiveWorkbook.Sheets("walk_product_macroneeds")
nCharts = ActiveSheet.ChartObjects.Count
i = 1

For Each myBox In ActiveSheet.CheckBoxes
myBox.LinkedCell = ActiveSheet.Range("M" & i).Address
i = i + 1
Next myBox

If Sh_2.Range("M1") = True Then
For iChart = 1 To nCharts
MinMaxNum = 4 + iChart
'Required to name chart objects, to do this hold shift and click
'on a chart, then give name in the name box hit enter
ActiveSheet.ChartObjects("UP" & iChart & "").Activate
ActiveChart.ChartArea.Select
SCount = ActiveChart.SeriesCollection.Count

With Sh_2.ChartObjects("UP" & iChart & "").Chart

ActiveChart.SeriesCollection(SCount).Trendlines.Add(Type:=xlPolynomial, Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
False).Select
With Selection.Border
.ColorIndex = 4
.Weight = xlThin
.LineStyle = xlContinuous
End With


End With
Next
ActiveChart.Deselect
End If

' ActiveSheet.ChartObjects("Chart 2").Activate
' ActiveChart.ChartArea.Select
' Count1 = ActiveChart.SeriesCollection(1).Trendlines.Count
' If Count1 > 0 Then
' For i = 1 To Count1
' ActiveChart.SeriesCollection(1).Trendlines(1).delete
' Next
' End If
' Count2 = ActiveChart.SeriesCollection(2).Trendlines.Count
' If Count2 > 0 Then
' For i = 1 To Count2
' ActiveChart.SeriesCollection(2).Trendlines(1).delete
' Next
' End If
' ActiveWindow.Visible = False


If Sh_2.Range("M1") = False Then
For iChart = 1 To nCharts
MinMaxNum = 4 + iChart
ActiveSheet.ChartObjects("UP" & iChart & "").Activate
ActiveChart.ChartArea.Select
Count1 = ActiveChart.SeriesCollection(1).Trendlines.Count
If Count1 > 0 Then
For i = 1 To Count1
'If ActiveChart.SeriesCollection(1).Trendlines(2).Type = xlPolynomial Then
If 3 = xlPolynomial Then
ActiveChart.SeriesCollection(1).Trendlines(2).delete
End If
Next
End If

ActiveWindow.Visible = False
Next
ActiveChart.Deselect
End If
'--------------------------------------------------------------------------------

Exit Sub 'Include this line to unsure that if the code runs without problem it won’t hit the error handler
FileError:
'Include Your Error Handling For not being able to open the file
Exit Sub 'Include this line to unsure that if the code runs without problem it won’t hit the error handler
ErrorHandler:
'Include Your Error Handling Code Here
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume Next 'this will tell the Program to continue on with the rest of the code.

End Sub

'------------------------- Trendline Linear -----------------------------------
Sub trendline_line()
On Error GoTo FileError
'Place this right before the line that may cause a file error
'Include File opening code here
On Error GoTo ErrorHandler 'Place this right after the line that may cause a file error to resume regular error handling
Dim myBox As CheckBox
Dim i As Integer
Set Sh_1 = ActiveWorkbook.Sheets("Lists")
Set Sh_2 = ActiveWorkbook.Sheets("Utilization_by_Product_Only")
Set Sh_3 = ActiveWorkbook.Sheets("walk_product_macroneeds")
nCharts = ActiveSheet.ChartObjects.Count
i = 1

For Each myBox In ActiveSheet.CheckBoxes
myBox.LinkedCell = ActiveSheet.Range("M" & i).Address
i = i + 1
Next myBox

If Sh_2.Range("M2") = True Then
For iChart = 1 To nCharts
MinMaxNum = 4 + iChart
'Required to name chart objects, to do this hold shift and click
'on a chart, then give name in the name box hit enter
ActiveSheet.ChartObjects("UP" & iChart & "").Activate
ActiveChart.ChartArea.Select

With Sh_2.ChartObjects("UP" & iChart & "").Chart

ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, _
Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
False).Select

' SCount = ActiveChart.SeriesCollection.Count
' ActiveChart.SeriesCollection(SCount).Trendlines(SCount).Select

With Selection.Border
.ColorIndex = 5
.Weight = xlThin
.LineStyle = xlContinuous
End With


End With
Next
ActiveChart.Deselect
End If
If Sh_2.Range("M2") = False Then
For iChart = 1 To nCharts
MinMaxNum = 4 + iChart
ActiveSheet.ChartObjects("UP" & iChart & "").Activate
ActiveChart.ChartArea.Select

Count1 = ActiveChart.SeriesCollection(1).Trendlines.Count
SCount = ActiveChart.SeriesCollection.Count

If Count1 > 0 Then
For i = 1 To Count1
If ActiveChart.SeriesCollection(SCount).Trendlines(SCount).Type = xlLinear Then
ActiveChart.SeriesCollection(SCount).Trendlines(SCount).delete
End If

Next
End If
' Count2 = ActiveChart.SeriesCollection(2).Trendlines.Count
' If Count2 > 0 Then
' For i = 1 To Count2
' ActiveChart.SeriesCollection(2).Trendlines(1).delete
' Next
' End If
ActiveWindow.Visible = False
Next
'ActiveChart.Deselect
End If
'--------------------------------------------------------------------------------

Exit Sub 'Include this line to unsure that if the code runs without problem it won’t hit the error handler
FileError:
'Include Your Error Handling For not being able to open the file
Exit Sub 'Include this line to unsure that if the code runs without problem it won’t hit the error handler
ErrorHandler:
'Include Your Error Handling Code Here
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume Next 'this will tell the Program to continue on with the rest of the code.

End Sub

 

[Andrew Poulsom]

A short example that deletes a trendline if it's polynomial:

Sub Test()
    Dim Count1 As Integer
    Dim i As Integer
    With ActiveChart.SeriesCollection(1)
        Count1 = .Trendlines.Count
        If Count1 > 0 Then
            For i = 1 To Count1
                If .Trendlines(Count1).Type = xlPolynomial Then
                    .Trendlines(Count1).Delete
                End If
            Next i
        End If
    End With
End Sub

If the trendline is linear the Type constant is xlLinear.

 

[herbertheien]

Thanks for the quick reply Andrew!

I have actually tried this code, I may have even copied it from one of your other posts. But the problem that I have is that when I have both a linear Trend line AND a Polynomial Trend Line then it skips over the ".Trendlines(Count1).delete" part of the code, which is exactly what I want it to do. The Count1 = 2 so it passes through the loop twice ok, but on both times through, it skips over the delete part of the code. The Trend Lines can be added in any order, unless there is a sneaky way to allow both to be up, but to keep track of the order inwhich they were put on the graph, so that I could reference them that way.

If .Trendlines(Count1).Type = xlPolynomial Then
.Trendlines(Count1).delete
End If

Many Thanks on any advice,
Herb

 

[Andrew Poulsom]

Silly me, Count1 should be i:

Sub Test()
    Dim Count1 As Integer
    Dim i As Integer
    With ActiveChart.SeriesCollection(1)
        Count1 = .Trendlines.Count
        If Count1 > 0 Then
            For i = Count1 To 1 Step -1
                If .Trendlines(i).Type = xlPolynomial Then
                    .Trendlines(i).Delete
                End If
            Next i
        End If
    End With
    With ActiveChart.SeriesCollection(1)
        Count1 = .Trendlines.Count
        If Count1 > 0 Then
            For i = Count1 To 1 Step -1
                If .Trendlines(i).Type = xlLinear Then
                    .Trendlines(i).Delete
                End If
            Next i
        End If
    End With
End Sub

 

[herbertheien]

Please disregard this post, I have to play with the answer above. Andrew is fast!





The order in which I put the Trend Lines on matters.

So for example:
If I insert a xlLinear Trend Line first, and then put an xlPolynomial Trend Line second, then during execution of the code it goes inside the if statement
"If .Trendlines(Count1).Type = xlPolynomial Then"
just fine.

However, when it encounters the second if statement
"If .Trendlines(Count1).Type = xlLinear Then"
it gives me an error message
"Run-time error '1004':"
"Application-defined or object-defined error"
so it fails.

If I put in the poly first, then it skips over the xlPolynomial constant and exectues the xlLinear fine, but then on the second pass of the loop it fails on the xlPoly constant with the same error message as above.

If I could just figure out a way to select a specific trend line when there are multiple trendlines on a chart, put in any order, I think I would be fine.



Sub Test()
Dim Count1 As Integer
Dim i As Integer
Dim Count2 As Integer

With ActiveChart.SeriesCollection(1)
Count1 = .Trendlines.Count
'Count2 = .SeriesCollection.Count

If Count1 > 0 Then
For i = 1 To Count1
If .Trendlines(Count1).Type = xlPolynomial Then
.Trendlines(Count1).delete
End If
If .Trendlines(Count1).Type = xlLinear Then
.Trendlines(Count1).delete
End If

Next i
End If
End With
End Sub

 

[herbertheien]

Andrew you have just made me a very happy man. I am actually giddy over VBA? Below is how I used the code that Andrew gave me.

I have two check boxes, one putting on a polynomial, and another for putting on a linear trend line. I have them linked to cells M1 and M2 in my spreadsheet. M1 - Polynomial M2- Linear. Below is how I used his code.



'----------------------------- Polynomial Trend Line ---------------------------------------
Sub trendline_poly()
On Error GoTo FileError
'Place this right before the line that may cause a file error
'Include File opening code here
On Error GoTo ErrorHandler 'Place this right after the line that may cause a file error to resume regular error handling
Dim myBox As CheckBox
Dim i As Integer
Dim j As Integer

Set Sh_1 = ActiveWorkbook.Sheets("Lists")
Set Sh_2 = ActiveWorkbook.Sheets("Utilization_by_Product_Only")
Set Sh_3 = ActiveWorkbook.Sheets("walk_product_macroneeds")
nCharts = ActiveSheet.ChartObjects.Count
i = 1

For Each myBox In ActiveSheet.CheckBoxes
myBox.LinkedCell = ActiveSheet.Range("M" & i).Address
i = i + 1
Next myBox
If Sh_2.Range("M1") = True Then
For iChart = 1 To nCharts
MinMaxNum = 4 + iChart
'Required to name chart objects, to do this hold shift and click
'on a chart, then give name in the name box hit enter
ActiveSheet.ChartObjects("UP" & iChart & "").Activate
ActiveChart.ChartArea.Select

With Sh_2.ChartObjects("UP" & iChart & "").Chart
ActiveChart.SeriesCollection(SCount).Trendlines.Add(Type:=xlPolynomial, Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
False).Select
With Selection.Border
.ColorIndex = 4
.Weight = xlThin
.LineStyle = xlContinuous
End With
End With
Next
ActiveChart.Deselect
End If


If Sh_2.Range("M1") = False Then
For iChart = 1 To nCharts
MinMaxNum = 4 + iChart
'Required to name chart objects, to do this hold shift and click
'on a chart, then give name in the name box hit enter
ActiveSheet.ChartObjects("UP" & iChart & "").Activate
ActiveChart.ChartArea.Select

'Many Thanks Andrew!
With ActiveChart.SeriesCollection(1)
Count1 = .Trendlines.Count
If Count1 > 0 Then
For i = Count1 To 1 Step -1
If .Trendlines(i).Type = xlPolynomial Then
.Trendlines(i).delete
End If
Next i
End If
End With
Next
ActiveChart.Deselect
End If

Exit Sub 'Include this line to unsure that if the code runs without problem it won’t hit the error handler
FileError:
'Include Your Error Handling For not being able to open the file
Exit Sub 'Include this line to unsure that if the code runs without problem it won’t hit the error handler
ErrorHandler:
'Include Your Error Handling Code Here
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume Next 'this will tell the Program to continue on with the rest of the code.

End Sub

'------------------------- Trendline Linear -----------------------------------
Sub trendline_line()
On Error GoTo FileError
'Place this right before the line that may cause a file error
'Include File opening code here
On Error GoTo ErrorHandler 'Place this right after the line that may cause a file error to resume regular error handling
Dim myBox As CheckBox
Dim i As Integer
Set Sh_1 = ActiveWorkbook.Sheets("Lists")
Set Sh_2 = ActiveWorkbook.Sheets("Utilization_by_Product_Only")
Set Sh_3 = ActiveWorkbook.Sheets("walk_product_macroneeds")
nCharts = ActiveSheet.ChartObjects.Count
i = 1

For Each myBox In ActiveSheet.CheckBoxes
myBox.LinkedCell = ActiveSheet.Range("M" & i).Address
i = i + 1
Next myBox
If Sh_2.Range("M2") = True Then
For iChart = 1 To nCharts
MinMaxNum = 4 + iChart
'Required to name chart objects, to do this hold shift and click
'on a chart, then give name in the name box hit enter
ActiveSheet.ChartObjects("UP" & iChart & "").Activate
ActiveChart.ChartArea.Select

With Sh_2.ChartObjects("UP" & iChart & "").Chart

ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, _
Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
False).Select

With Selection.Border
.ColorIndex = 5
.Weight = xlThin
.LineStyle = xlContinuous
End With

End With
Next
ActiveChart.Deselect
End If


If Sh_2.Range("M2") = False Then
For iChart = 1 To nCharts
MinMaxNum = 4 + iChart
'Required to name chart objects, to do this hold shift and click
'on a chart, then give name in the name box hit enter
ActiveSheet.ChartObjects("UP" & iChart & "").Activate
ActiveChart.ChartArea.Select

'Many Thanks Andrew!
With ActiveChart.SeriesCollection(1)
Count1 = .Trendlines.Count
If Count1 > 0 Then
For i = Count1 To 1 Step -1
If .Trendlines(i).Type = xlLinear Then
.Trendlines(i).delete
End If
Next i
End If
End With

Next
ActiveChart.Deselect
End If

Exit Sub 'Include this line to unsure that if the code runs without problem it won’t hit the error handler
FileError:
'Include Your Error Handling For not being able to open the file
Exit Sub 'Include this line to unsure that if the code runs without problem it won’t hit the error handler
ErrorHandler:
'Include Your Error Handling Code Here
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume Next 'this will tell the Program to continue on with the rest of the code.

End Sub