Detecting Sheet Type with VBA

[Type of Sheet displayed]

I am trying to determine the type of sheet (chart or worksheet) that is
currently active. I was filtering through the documentation and the
xlsheettype seems to be one that I could use but not having much luck with
it. I have 38 sheets in this application and I'm configuring the workbook
when opened. Some things I'm turing off like the column and row headings but
that doesn't apply to a chart sheet. Anyone have a suggestion?

Thanks,

Bryan

 

[kassie]

You can try
If TypeName(ActiveSheet) ="Chart" or "Worksheet". However, when you spell
Chart as cHart, it will not recognise it. To solve this problem, you can
change your statement to read
If Ucase(TypeName(ActiveSheet)) = Ucase("Chart")

 

[Gene]

Here's an alternate approach Brian,

Sub checksheets()

Dim activsht As Variant
Dim activtype As Integer

' check if worksheet
For Each activsht In ActiveWorkbook.Sheets
activtype = activsht.Type

'' -4167 is the type code for a worksheet, 3 is the type code for a chart
If activtype = -4167 Then
'' do some action
MsgBox activsht.Name
Else
'' do something else
MsgBox activsht.Name
End If

Next activsht

End Sub

substitute the msgboxes for the code you want to run.

 

[Joergen Bondesen]

Hi Bryan.

Try belowe, please.

Sub shtyp()
MsgBox TypeName(ActiveSheet)
End Sub

'or

Sub SheetTypes()
Dim sh As Object
For Each sh In ActiveWorkbook.Sheets
msgbox TypeName(sh)
Next
End Sub


--
Best Regards
Joergen Bondesen

"Type of Sheet displayed" <Type of Sheet
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Type of Sheet displayed]

Kassie,

Thank you very much. That worked great!
-- Bryan

 

[Type of Sheet displayed]

Gene,

Thank you also, that works as well.

-- Bryan

 

[Type of Sheet displayed]

Joergen,

As with the others, thank you much. This worked very well also.

Bryan

 

[Dave Peterson]

Sometimes using the excel constants will help when you read the code later:

xlWorksheet is the same as -4167

 

[NickHK]

If you only want to work with the Worksheets collection instead of the
Sheets collection, so Charts are not even included.
Dim WS as WorkSheet
For Each WS in Worksheets
'etc

NickHK