check if worksheet exists
 

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!

check if worksheet exists
 

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
------------------------
Hope this helps...

Chong Moua

-----Original Message-----
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!
.

check if worksheet exists
 

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

"Craig Wilks" wrote in message
...
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!