Detecting Sheet Type with VBA
 

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

Detecting Sheet Type with VBA
 

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")

"Type of Sheet displayed" wrote:

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

Detecting Sheet Type with VBA
 

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.
--
Gene


"Type of Sheet displayed" wrote:

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

Detecting Sheet Type with VBA
 

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

Detecting Sheet Type with VBA
 

Kassie,

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

"kassie" wrote:

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")

"Type of Sheet displayed" wrote:

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

Detecting Sheet Type with VBA
 

Gene,

Thank you also, that works as well.

-- Bryan

"Gene" wrote:

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.
--
Gene


"Type of Sheet displayed" wrote:

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

Detecting Sheet Type with VBA
 

Joergen,

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

Bryan

"Joergen Bondesen" wrote:

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

Detecting Sheet Type with VBA
 

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

xlWorksheet is the same as -4167



Gene wrote:

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.
--
Gene

"Type of Sheet displayed" wrote:

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

--

Dave Peterson

Detecting Sheet Type with VBA
 

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

"Type of Sheet displayed"
wrote in message ...
Joergen,

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

Bryan

"Joergen Bondesen" wrote:

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