Does worksheet exist

R

Robert S

I'm looking for a way to get a boolean result if a given
worksheet name already exists - for example if workbook
text.xls contains sheet1, sheet2, and sheet3, then if I
pass a variable containing the worksheet 'sheet4', I would
get a false value returned. Any help would be appreciated.

Thanks
 
H

Harald Staff

Function SheetXist(ShName As String) As Boolean
On Error Resume Next
SheetXist = Len(Sheets(ShName).Name)
End Function

Sub Test()
MsgBox SheetXist("Sheet1")
MsgBox SheetXist("Sheet4501")
End Sub

HTH. Best wishes Harald
 
P

pfsardella

From a prior posting by Chip Pearson :

Function SheetExists(SheetName As String) As Boolean
On Error Resume Next
SheetExists = CBool(Len(ThisWorkbook.Worksheets(SheetName).Name))
End Function

If SheetExists("Sheet1") = True Then
' do something
Else
' do something else
End If

HTH
Paul
 
B

Bob Phillips

Here is a function


'-----------------------------------------------------------------
Function IsWsSheet(Sh As String, Optional WB As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If WB Is Nothing Then Set WB = ActiveWorkbook
On Error Resume Next
Set oWs = WB.Worksheets(Sh)
On Error GoTo 0
IsWsSheet = Not oWs Is Nothing
End Functi

--

HTH

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