Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function
Use it like this for example
Sub Sheet_Test()
Dim SName As String
If SheetExists("test") = False Then
ActiveSheet.Name = "test"
Else
MsgBox "sorry the sheet exist"
End If
End Sub
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl
"Ross" <rosso...@phs.com> wrote in message news:05db01c3522d$e7b5e790$a101...@phx.gbl...
> how would you go about checking to see if a sheet exists
> of not?
Try something like
Function SheetExists(SheetName As String, _
Optional Book As Workbook) As Boolean
Dim WB As Workbook
Dim N As Long
On Error Resume Next
If Book Is Nothing Then
Set WB = ThisWorkbook
End If
N = Len(WB.Worksheets(SheetName).Name)
SheetExists = (N <> 0)
End Function
You can then call this with code like
If SheetExists("Sheet123") = True Then
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
>.
>
To pass a workbook reference to the function, do something like
If SheetExists("Sheet123",Workbooks("Book2.xls")) = True Then
This will test whether Sheet123 exists in Book2, regardless of
what workbook is active or what workbook the code resides in.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"Ross" <rosso...@phs.com> wrote in message
news:028c01c352d3$d893d550$a101...@phx.gbl...