Modify moving average trendline macro

[artz]

The following code is handy for adding a moving average trendline to a cahrt. It served me well until I added two additional charts to my worksheet. Now, if I update cell P5 with a new value and call this sub with a CommandButton click, all three charts update which is not what I want.

Sub mov_avg()
' Procedure to show moving avg for user specified period
' Cycles through all charts, applies same criteria to each chart
Dim chtobj As ChartObject
Dim Msg As String
Dim n As Integer
n = ActiveSheet.ChartObjects.Count
' User choices:
'   1. Show data series?
'   2. Moving Avg Period? If 0, no moving avg
    per = Range("P5")
'**** Set moving Averages
On Error Resume Next
For Each chtobj In ActiveSheet.ChartObjects
   With chtobj.Chart
   'cht_num = chtobj.Index
   cht_num = 1
    ' Remove previous trendlines
    ' Check to see how many trend lines
        n_tr = .SeriesCollection(1).Trendlines.Count
      If n_tr > 0 Then
            For cnt = 1 To n_tr
                 .SeriesCollection(1).Trendlines(cnt).Delete
             Next cnt
       End If
     ' Check to see if period > 0; if yes, add trend line
        If per > 0 Then
        .SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:=per _
        , Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False).Select
        With .SeriesCollection(1).Trendlines(1).Border
            .ColorIndex = 5
            .Weight = xlMedium
            '.LineStyle = xlHairline
        End With
        End If
' Check to see if data series to be plotted
         If Range("c7") = "Off" Then
            With .SeriesCollection(1).Border
                .ColorIndex = 3
                .Weight = xlHairline
                .LineStyle = xlNone
            End With
         Else
            With .SeriesCollection(1).Border
                .ColorIndex = 1
                .Weight = xlThin
                '.LineStyle = xlAutomatic
            End With
        End If
    'End With
    End With
    Next chtobj
End Sub

To identify the the chart of interest that should be updated, the following code can locate the particular chart:

Public Sub chart_list()
Dim chtobj As ChartObject
Dim Msg As String
Dim n As Integer
n = ActiveSheet.ChartObjects.Count
Msg = "Chart List for Sheet " & vbTab & ActiveSheet.Name & vbTab & "No charts = " & n & vbCrLf & vbCrLf
Msg = Msg & "Name " & vbTab & vbTab & "Index" & vbTab & "Top Pos" & vbTab & "Left Pos " & vbTab & "Width " & vbTab & "Height" & vbCrLf
For Each chtobj In ActiveSheet.ChartObjects
    cht_width = chtobj.Width
    cht_height = chtobj.Height
    Top_Position = chtobj.Top
    Left_Position = chtobj.Left
    Msg = Msg & chtobj.Name & vbTab & vbTab & chtobj.Index & vbTab & Top_Position & vbTab & Left_Position & vbTab & cht_width & vbTab & cht_height & vbCrLf
Next chtobj
out = MsgBox(Msg, , "Chart List")
End Sub

In this case, I was able to determine that the chart to be updated is .ChartObjects("Chart 32").Chart

Can anyone in the Forum help to modify the mov_avg sub to only update .ChartObjects("Chart 32").Chart when the mov_avg sub is called?

Thanks,

Art

 

[AlphaFrog]

Replace this...
For Each chtobj In ActiveSheet.ChartObjects

With this...
Set chtobj = ActiveSheet.ChartObjects("Chart 32").Chart

And delete this...
Next chtobj

 

[artz]

AlphaFrog,

Thanks for your code suggestions. When I made your changes to the macro and ran it, the good news is that it no longer tries to update all three charts.

The bad news is that it doesn't make changes to the intended chart either. I changed the value in cell P5 and clicked on the CommandButton and no action occurs.

Any suggestions?

Thanks,

Art

 

[AlphaFrog]

Try this...
Set chtobj = ActiveSheet.ChartObjects("Chart 32")

Instead of this...
Set chtobj = ActiveSheet.ChartObjects("Chart 32").Chart
(delete the red text)

 

[artz]

Thanks, AlphaFrog, that works just fine. -Art