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