check if worksheet exists

[Craig Wilks]

What is the code to check if a worksheet exists once the workbook is
activated?

Craig Wilks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Chong Moua]

Hi Craig,

Here's one way...
------------------------
Sub SheetExist()

On Error GoTo ErrorTrap

Sheets("MySheet").Select
MsgBox "Sheet does exist!"
Exit Sub

ErrorTrap:
MsgBox "Sheet does not exist!"

End Sub

 

[Robin Hammond]

This is a rather exhaustive way of doing it that allows you to specify
either index or name within the function. The default is name.

Sub Test()
MsgBox (WorksheetExists(1, False))
MsgBox (WorksheetExists("Sheet1"))
MsgBox (WorksheetExists("Sheet1", True))
MsgBox (WorksheetExists("Chart1"))
End Sub

Function WorksheetExists(vName As Variant, Optional bName As Boolean = True)
Dim shTest As Worksheet
Dim chTest As Chart

On Error Resume Next
If bName = True Then
Set shTest = Sheets(CStr(vName))
Set chTest = Sheets(CStr(vName))
Else
Set shTest = Sheets(CInt(vName))
Set chTest = Sheets(CInt(vName))
End If
On Error GoTo 0
If Not (shTest Is Nothing And chTest Is Nothing) Then WorksheetExists = True
Set shTest = Nothing
Set chTest = Nothing
End Function

Robin Hammond
www.enhanceddatasystems.com