check if worksheet exists

C

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
 
R

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
 
Top