Loop through worksheets and format chart 1: run time error

[suremac]

Greetings,


I wrote a chart formatting macro, which works when applied to a specific sheet, and I'm trying use it in a for each loop. The macro is supposed to loop through a list of sheets and only format chart 1. I'm getting a "run time error 438: object doesn't support this property or method" at this line:

 .ActiveChart.Shapes("TextBox 1").TextFrame.Characters.Text = "Bodily Injury (BI) Liability Claim Trends" & vbLf & " 2005-" & Range("K5").Value & " Percentage Change"

Here is my for each loop:

Sub UpdateTextAllStateCharts()
    'This macro loops through worksheets in a list and performs the assigned task
    Dim sheet_name As Range
    For Each sheet_name In Sheets("WS").Range("C:C")
        If sheet_name.Value = "" Then
            Exit For
        Else
            With Sheets(sheet_name.Value)
                .ChartObjects("Chart 1").Activate
                .ActiveChart.Shapes("TextBox 1").TextFrame.Characters.Text = "Bodily Injury (BI) Liability Claim Trends" & vbLf & " 2005-" & Range("K5").Value & " Percentage Change" 
               .ChartObjects("Chart 1").Activate
               With ActiveChart.Shapes("TextBox 1").TextFrame.Characters
               With .Font
               .Name = "Calibri"
               .Size = 14
               .FontStyle = "Regular"
               End With
               With ActiveChart.Shapes("TextBox 1").TextFrame
               With .Characters(42, 68).Font
              .FontStyle = "Italic"
              .Size = 12
              End With
              End With
              End With
            End With
        End If
        Next sheet_name
    End Sub

This line is also causing the same error:

With .Characters(42, 68).Font

Thanks in advance for any help.


Regards,

 

[JoeMo]

Does every sheet have a Chart 1? Does every Chart 1 have a TextBox 1 associated with it?
Does every TextBox 1 contain at least 100 characters?

 

[suremac]

Hi JoeMo, thanks for the reply. To your questions: yes, yes, and no. Each TextBox 1 contains 68 characters. With a lot of trial and error, this solved the first error:

Sheets(sheet_name.Value).Chartobjects("Chart 1").Chart.Shapes("TextBox1").TextFrame.Characters.Text = "Bodily Injury (BI) Liability Claim Trends" & vbLf & " 2005-" & Range("K5").Value & " Percentage Change"

Not sure why though. I moved around the

end with

statements to solve the second error.