VBA test for the existance of a worksheet.

[Tom Peacock]

I am looking for a true or false test in Excel 2000 VBA to indicate wheather
a sheet name exists in a workbook. Any ideas?

 

[Harald Staff]

Hi Tom

Function SheetXists(sName As String) As Boolean
On Error Resume Next
SheetXists = Sheets(sName).Index
On Error GoTo 0
End Function

Sub test()
MsgBox SheetXists("Sheet1")
MsgBox SheetXists("YetAnotherSummary")
End Sub

HTH. Best wishes Harald

 

[Dr. Stephan Kassanke]

I am looking for a true or false test in Excel 2000 VBA to indicate
wheather a sheet name exists in a workbook. Any ideas?

Tom,

I don't know where this comes from, no clue about the author but it should
solve your problem.

Function SheetExists(sname) As Boolean
' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function


cheers,
Stephan

 

[Dave Peterson]

I like this from Chip Pearson:

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function


Then you can use:

if worksheetexists("sheet1", activeworkbook) then
'do something
else
'do something else
end if

 

[Tom Peacock]

Thanks to all for the quick replies. I now have a solution to the problem.