Is a sheet a worksheet?

[RB Smissaert]

Is there any better way (less code) to check if a sheet is a worksheet?

Function CheckIsWorksheet(strSheet As String) As Boolean

Dim sh As Object

For Each sh In ActiveWorkbook.Worksheets
If sh.Name = strSheet Then
CheckIsWorksheet = True
Exit Function
End If
Next sh

CheckIsWorksheet = False

End Function


RBS

 

[Bob Phillips]

Bit shorter, and no looping

Function CheckIsWorksheet(strSheetAs String)
Dim sh As Worksheet

On Error Resume Next
Set sh = Worksheets(strSheetAs )
CheckIsWorksheet = Not sh Is Nothing

End Function

 

[Bob Phillips]

Sorry, should be

Function CheckIsWorksheet(strSheet As String)
Dim sh As Worksheet

On Error Resume Next
Set sh = Worksheets(strSheet )
CheckIsWorksheet = Not sh Is Nothing

End Function

 

[Bill]

Use typename.

Sub CheckifSheet()
If TypeName(ActiveSheet) = "Worksheet" Then
MsgBox "Is worksheet"
Else
MsgBox "Is Not Worksheet"
End If
End Sub

If typename(activesheet)=worksheet then

 

[RB Smissaert]

Thanks, I like that one.
Didn't know about TypeName.

This suits my purpose best:

Function CheckIfWorkSheet(strSheet As String) As Boolean
If TypeName(Sheets(strSheet)) = "Worksheet" Then
CheckIfWorkSheet = True
Else
CheckIfWorkSheet = False
End If
End Function

RBS

 

[Bob Phillips]

RB

Better

Function CheckIfWorkSheet(strSheet As String) As Boolean
CheckIfWorkSheet = TypeName(Sheets(strSheet)) = "Worksheet"
End Function

 

[RB Smissaert]

Bob,

Yes, thanks, you are right there.
Tend to forget this way of doing this.

RBS