Worksheet Exists...

[James Weaver]

How do you find out if a worksheet exists or not within a
workbook? I have some code which should only work if a
worksheet (with a known name) exists in the user's
workbook.

Thanks.

 

[Chip Pearson]

James,

Try the following function:

Function WorksheetExists(WSName As String, _
Optional WB As Workbook) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, _
ActiveWorkbook, WB).Worksheets(WSName).Name))
End Function


You can then call this in code with something like

If WorksheetExists("Sheet1",ThisWorkbook) = True Then
' whatever
Else
' whatever else
End If

 

[Bob Phillips]

James,

Here's one way

Dim oWS As Worksheet

On Error Resume Next
Set oWS = Activeworkbook.Worksheets("Sheet1")
On Error GoTo 0
If oWS Is Nothing Then
MsgBox "Worksheet does not exist"
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[James Weaver]

Thanks - very effective!

-----Original Message-----
James,

Here's one way

Dim oWS As Worksheet

On Error Resume Next
Set oWS = Activeworkbook.Worksheets("Sheet1")
On Error GoTo 0
If oWS Is Nothing Then
MsgBox "Worksheet does not exist"
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.