for each chart (sheet and embedded)
 

I'd like to change a chart setting for every chart (both chart sheets
and charts embedded in worksheets) in a workbook. I modified the below
code from Tom Ogilvy (thanks!), but I get an error when I try to modify
the chart settings with "cht.PlotVisibleOnly = False". If I change
"cht" to type Chart instead of type Object, the line would work, but
then I get a type mismatch error at "For Each cht In sh.ChartObjects".
Any ideas? Thanks!

Sub tester1()
Dim cht As Object, sh as Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.ChartObjects.Count 0 Then
sh.Activate
For Each cht In sh.ChartObjects
cht.Select
cht.PlotVisibleOnly = False
Next
End If
Next
For Each cht In ThisWorkbook.Charts
cht.Activate
cht.PlotVisibleOnly = False
Next
End Sub

for each chart (sheet and embedded)
 

Hi,

Try this revision,

Sub tester1()
Dim cht As Object, sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
If sh.ChartObjects.Count 0 Then
For Each cht In sh.ChartObjects
cht.Chart.PlotVisibleOnly = False
Next
End If
Next
For Each cht In ThisWorkbook.Charts
cht.PlotVisibleOnly = False
Next

End Sub

Cheers
Andy

wrote:
I'd like to change a chart setting for every chart (both chart sheets
and charts embedded in worksheets) in a workbook. I modified the below
code from Tom Ogilvy (thanks!), but I get an error when I try to modify
the chart settings with "cht.PlotVisibleOnly = False". If I change
"cht" to type Chart instead of type Object, the line would work, but
then I get a type mismatch error at "For Each cht In sh.ChartObjects".
Any ideas? Thanks!

Sub tester1()
Dim cht As Object, sh as Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.ChartObjects.Count 0 Then
sh.Activate
For Each cht In sh.ChartObjects
cht.Select
cht.PlotVisibleOnly = False
Next
End If
Next
For Each cht In ThisWorkbook.Charts
cht.Activate
cht.PlotVisibleOnly = False
Next
End Sub


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info