How to find the type of Sheet in Excel.Workbook.sheets collection

  • Thread starter Thread starter Raj
  • Start date Start date
R

Raj

Hi,

How can I find out what type of sheet ( Chart, Dialog, worksheet ) is
present in Excel workbook.Sheets collection. I need to perform two
different operations based on the type.

Thanks,
 
Raj,

The obvious way is to check the sheets type property, but that deoesn't work
even though there are a whole set of type constants.

The previous post from Rob Bovey gives a safer method

'-------------------------

The Type property is only useful for distinguishing between regular
worksheets and the two types of XLM macro sheets. I don't really know why
they have constants in there for Charts and DialogSheets, but they don't
serve any purpose that I know of (the DialogSheet object doesn't even have a
Type property, as you've discovered).

Instead, you could use the TypeName function to return the string which
identifies the sheet type, then when you hit a worksheet use the Type
property to determine what type of sheet it is:

Sub ListSheetTypes()
Dim objSheet As Object
Dim szType As String
For Each objSheet In ActiveWorkbook.Sheets
szType = TypeName(objSheet)
If szType = "Worksheet" Then
Select Case objSheet.Type
Case xlWorksheet
Debug.Print szType
Case xlExcel4MacroSheet
Debug.Print "Excel4MacroSheet"
Case xlExcel4IntlMacroSheet
Debug.Print "Excel4IntlMacroSheet"
End Select
Else
Debug.Print szType
End If
Next objSheet
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/


'--------------------------------------

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Raj said:
Hi,

How can I find out what type of sheet ( Chart, Dialog, worksheet ) is
present in Excel workbook.Sheets collection. I need to perform two
different operations based on the type.
Click to expand...

Hi Raj,

Here's one way:

Dim objSheet As Object
For Each objSheet In ActiveWorkbook.Sheets
If TypeOf objSheet Is Excel.Worksheet Then
''' It's a worksheet.
ElseIf TypeOf objSheet Is Excel.Chart Then
''' It's a Chart sheet.
ElseIf TypeOf objSheet Is Excel.DialogSheet Then
''' It's a DialogSheet.
End If
Next objSheet

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Sub SheetTypes()
Dim sh As Object
For Each sh In ActiveWorkbook.Sheets
Debug.Print TypeName(sh)
Next
End Sub
 
Back
Top