Find Chart Sheets in workbook

[ExcelMonkey]

I am trying to loop through the sheets in a workbook and test to see if the
which sheets are chart sheets. The code is failing is this because of the
way I have set up the wrksht variable (i.e. dim as worksheet)?

Function IsChartSheet() As Boolean
Dim wrksht As Worksheet
Dim chrtsht As Chart
On Error Resume Next

For Each wrksht In ThisWorkbook
Set chrtsht = ActiveWorkbook.Charts(wrksht.Name)
If Err = 0 Then
IsChartSheet = True
Debug.Print ActiveSheet.Name
Debug.Print "This is a chart sheet!"
Else
IsChartSheet = False
Debug.Print ActiveSheet.Name
Debug.Print "This is not a chart sheet!"
End If
On Error GoTo 0
Set chrtsht = Nothing
Next
End Function


Thanks

EM

 

[Dave Peterson]

Worksheets can't be chart sheets.

maybe you could use:

Dim sht as Object
for each sht in thisworkbook.sheets
msgbox typename(sht)
next sht

So you can test it for:

if typename(sht) = "Chart" then
'it's a chart sheet.
end if

Be careful. There are other types of sheets besides worksheets and chart
sheets.

 

[Jon Peltier]

If you wanted to get the chart sheets, you could use this:

Dim Cht as Chart
For Each Cht In ActiveWorkbook.Charts
' Cht is a chart sheet
Next

- Jon