VBA test for the existance of a worksheet.
 

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

VBA test for the existance of a worksheet.
 

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" <tpeacockathoustondotrrdotcom skrev i melding
...
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

VBA test for the existance of a worksheet.
 

"Tom Peacock" <tpeacockathoustondotrrdotcom schrieb im Newsbeitrag
...
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

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

VBA test for the existance of a worksheet.
 

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 wrote:

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

--

Dave Peterson

VBA test for the existance of a worksheet.
 

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