How to check if a sheet exists ?

  • Thread starter Thread starter Luc
  • Start date Start date
Courtexy Chip Pearson:

Function SheetExists(SheetName As String, _
Optional WB As Workbook) As Boolean
Dim W As Workbook
If WB Is Nothing Then
Set W = ActiveWorkbook
Else
Set W = WB
End If
On Error Resume Next
SheetExists = CBool(Len(W.Worksheets(SheetName).Name))
End Function




Title says it all.
Luc
 
Hi,

I'd have a seperate fumction and call it to test for the existance of a sheet

Sub nn()
MsgBox SheetThere("sheet3")
End Sub


Function SheetThere(ShtName As String) As Boolean
Dim x
On Error GoTo GetMeOut
x = ActiveWorkbook.Sheets(ShtName).Name
SheetThere = True
Exit Function

GetMeOut:
SheetThere = False
End Function


Mike
 
Here is a shorter version of your "on error" method for this function...

Function SheetThere(ShtName As String) As Boolean
On Error Resume Next
SheetThere = Len(ActiveWorkbook.Sheets(ShtName).Name)
End Function
 
Dude ! ! ! ! I LOVE YOU ! ! ! ! I Have searched over the internet for 3 hours.. Only yours worked the best.. Im going to share this to everyone..
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

When a sheet is copied, i want also that the Sub's whithin this sheet is copied to the new sheet 2
How do i print all visible sheets ? 6
Case select returning error when cell contains #N/A : how must i avoid this error 2
Strange thing when copying a range !! 5
How do i check if the a named range is empty 3
ActiveWorkbook.Names.Add Name ??? 4
How do i check if the current cell is part of a range ? 2
Create a new worksheet (excel 2003) if one or more cells in a range is not empty 6

Back
Top