For each loop with charts

Posted by Chris on September 17, 2001 6:22 AM

I want to run some code against every chart on every sheet in a workbook. Can't seem to get the for each loop right, this is what i'm using:

dim wks as worksheet
dim cht as chart

for each wks in worksheets
wks.activate
for each cht in charts

(I insert the code I know works in here)

next cht
next wks

Can anyone tell me what i'm doing wrong?

Thanks, Chris

Posted by Russell Hauf on September 17, 2001 6:51 AM

Try this:

Dim wks As Worksheet
Dim cht As ChartObject

For Each wks In ActiveWorkbook.Worksheets
wks.Activate
For Each cht In ActiveSheet.ChartObjects
Debug.Print cht.Name
Next cht
Next wks

(note that this won't pick up charts that are their own sheet).

Hope this helps,

Russell

Posted by Chris on September 17, 2001 7:08 AM

Works a Treat - Thanks!

For Each wks In ActiveWorkbook.Worksheets wks.Activate For Each cht In ActiveSheet.ChartObjects Debug.Print cht.Name Next cht Next wks