For each chart loop not working

[Tom]

I am trying to modify a bunch of charts using a For each loop. However when I
check to count the number of charts I get 0. Here is my code:


MsgBox ActiveWorkbook.Charts.Count (this message show 0 charts)
' however this following message indicates the workbook contains a chart
named Chart 85

MsgBox ActiveWorkbook.Charts.Name (this message show "Chart 85")

So since there are 0 charts the for loop does not work


For Each mychart In ActiveWorkbook.Charts

 

[JLGWhiz]

I believe you have to use ChartObjects if the charts are embedded on your
worksheets. If they have their own page the you can just use Charts. See
VBA help for details.

 

[Doug Glancy]

Tom,

If the charts are in a worksheet, not on their own chart sheet, you have to
use the ChartObjects instead of charts. However, each ChartObject has it's
own chart object, which you use to access some of the charts properties.
For example, this

This code will cycle through each worksheet and tell you the some info about
the charts. You can see that some of the info, like Count and location, is
in ChartObject, some is in ChartObject.Chart.

Sub test()
Dim ws As Worksheet
Dim coChart As ChartObject

For Each ws In ActiveWorkbook.Worksheets
Debug.Print ws.Name & " has " & ws.ChartObjects.Count & " charts"
For Each coChart In ws.ChartObjects
Debug.Print " " & coChart.Name
Debug.Print " is located at " & coChart.TopLeftCell.Address
If coChart.Chart.HasTitle Then
Debug.Print " title is " & coChart.Chart.ChartTitle.Text
Else
Debug.Print " no title"
End If
Next coChart
Next ws
End Sub