Test whether a chartobject is on a worksheet

[Werner Rohrmoser]

Hello,

I need this test, because I have to adjust the legend and a text box
on my charts, which
always change their position (that's really annoying!).
In my workbook are also worksheets without embedded charts,
so I have to skip them.
Thanks.

Excel XP SP3
WIN XP SP1

Regards
Werner

 

[Gary''s Student]

Sub countum()
MsgBox (ActiveSheet.ChartObjects.Count)
End Sub

 

[Werner Rohrmoser]

Hi Gary's Student,

thanks, simple and good.
BTW, do you know how the legend and textbox position change on the
chartobject can be avoided.
Because adjusting the shapes is fighting agianst the symptom but not
treating the root cause.

Werner

P.S.: Who is Garry?

 

[Peter T]

Sub test()
Dim i As Long
Dim ws As Worksheet
Dim chtObj As ChartObject
Dim cht As Chart

For Each ws In ActiveWorkbook.Worksheets
For i = 1 To ws.ChartObjects.Count
Set cht = ws.ChartObjects(i).Chart
Debug.Print cht.Parent.Parent.Name, _
cht.Parent.Name, _
cht.HasLegend
Next
Next

End Sub

Although not typical embedded charts can also exist on a Chart sheet. If
that's a possibility -

Dim sht as Object
and change for each ws to
For each sht in Activeworkbook.Sheets

Regards,
Peter T

 

[Gary''s Student]

Gary was my Excel teacher.

I have never been able to stop objects from moving, but have always relied
on "remembering" where I want them, and then moving back there with Top or
Left.

 

[Werner Rohrmoser]

Thanks Peter,

I'm going to use your structure to loop through my sheets.

Regards
Werner