VBA test for the existance of a worksheet.

  • Thread starter Thread starter Tom Peacock
  • Start date Start date
T

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?
 
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
 
Tom Peacock said:
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?
Click to expand...
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
 
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
 
Back
Top