check if worksheet exists

  • Thread starter Thread starter Craig Wilks
  • Start date Start date
C

Craig Wilks

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

Craig Wilks
 
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
 
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
 
Back
Top