Moving Avg trendlines not working

[IronPalm]

Hi,


In my worksheet of the daily index I have added a couple of check-boxes to enable a 52 week and 26 week moving average trendline. But, I am unable to remove individual trendlines when I uncheck the boxes. The code for 52k avg is as follows (similarly for 26 wk avg) and I have attached the file down below.


Private Sub Wk52_MA_Change()

Dim c As Chart

Dim s As Series

Dim t As Trendline


ActiveSheet.ChartObjects("BDI_Data").Activate

Set c = ActiveChart

If ActiveSheet.Wk52_MA Then

With c.SeriesCollection(1)

.Trendlines.Add

.Trendlines(1).Select

.Trendlines(1).Name = "Wk52"

.Trendlines(1).Type = xlMovingAvg

.Trendlines(1).Period = 364

.Trendlines(1).Format.Line.DashStyle = msoLineDash

.Trendlines(1).Format.Line.Weight = 1.5

'.Trendlines(1).Format.Line.Color = "000000"

End With

Else

For Each s In c.SeriesCollection

If s.Trendlines.Count > 0 Then

For Each t In s.Trendlines

If t.Name = "Wk52" Then

t.Delete

End If

Next t

End If

Next s

End If

End Sub


http://rapidshare.com/files/409328405/Historical_data_Ver2.xlsm


Any help would be appreciated.

 

[Hui]

IronPalm


Try the following code which is yours with minimal modification

You cannot use a Moving Average with greater than 255 days or 36.4 weeks

[pre]

Private Sub Wk26_MA_Change()
Dim c As Chart
Dim s As Series
Dim t As Trendline

ActiveSheet.ChartObjects("BDI_Data").Activate
Set c = ActiveChart
'c.SeriesCollection
If ActiveSheet.Wk26_MA Then
With c.SeriesCollection(1)
.Trendlines.Add
.Trendlines(1).Select
.Trendlines(1).Name = "Wk26"
.Trendlines(1).Type = xlMovingAvg
.Trendlines(1).Period = 182
.Trendlines(1).Format.Line.DashStyle = msoLineDash
.Trendlines(1).Format.Line.Weight = 1.25
End With
Else
For Each t In c.SeriesCollection(1).Trendlines
If t.Name = "Wk26" Then
t.Delete
End If
Next t

End If
End Sub

Private Sub Wk52_MA_Change()
Dim c As Chart
Dim s As Series
Dim t As Trendline

ActiveSheet.ChartObjects("BDI_Data").Activate
Set c = ActiveChart
If ActiveSheet.Wk52_MA Then
With c.SeriesCollection(1)
.Trendlines.Add
.Trendlines(1).Select
.Trendlines(1).Name = "Wk52"
.Trendlines(1).Type = xlMovingAvg
.Trendlines(1).Period = 255 '36.43 weeks
.Trendlines(1).Format.Line.DashStyle = msoLineDash
.Trendlines(1).Format.Line.Weight = 1.5
End With
Else
For Each s In c.SeriesCollection
If s.Trendlines.Count > 0 Then
For Each t In s.Trendlines
If t.Name = "Wk52" Then
t.Delete
End If
Next t
End If
Next s
End If
End Sub

[/pre]