Find Chart Sheets in workbook
 

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

Find Chart Sheets in workbook
 

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.



ExcelMonkey wrote:

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

Find Chart Sheets in workbook
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dave Peterson" wrote in message
...
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.



ExcelMonkey wrote:

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