Checking to see if a worksheet exists

[Raman325]

What is the best way to check whether a given worksheet exists? For
example, I would like to know whether the "Week 1" worksheet exists
without throwing an error to the user. Thanks in advance for your help.

 

[Dnereb]

use error supression like this:


Code
-------------------
Function IsWorksheetPresent(Name As String) As Boolean

Dim Ws As Worksheet

On Error Resume Next

For Each Wb In ThisWorkbook.Worksheets
Err.Clear
If UCase(Ws.Name) = Name Then
On Error GoTo 0
IsWorksheetPresent = True
Exit Function
End If
Next
On Error GoTo 0
End Functio
-------------------


this will return a True if the worksheet is present
you could use it like this:


Code

 

[Raman325]

Great, thanks for your help

 

[Norman Jones]

Hi Raman325,

Try:

Function SheetExists(sName As String, _
Optional ByVal wb As Workbook) As Boolean
On Error Resume Next
If wb Is Nothing Then Set wb = ActiveWorkbook
SheetExists = CBool(Len(sheets(sName).Name))
End Function

 

[Rob Bovey]

What is the best way to check whether a given worksheet exists? For
example, I would like to know whether the "Week 1" worksheet exists
without throwing an error to the user. Thanks in advance for your help.

Here's a function you can use to check for this. It assumes you're
looking for a sheet in the currently active workbook.

Function bSheetExists(ByRef szSheetName As String) As Boolean
On Error Resume Next
bSheetExists = Not (ActiveWorkbook.Sheets(szSheetName) Is Nothing)
End Function

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Jim Thomlinson]

Does that code function correctly... I like the optional workbook argument
but I think you need to change the line.

SheetExists = CBool(Len(sheets(sName).Name))
to
SheetExists = CBool(Len(wb.sheets(sName).Name))

Otherwise this function will look at the active workbook won't it?

 

[Norman Jones]

Hi Jim,

Thank you.

I added the workbook argument but omitted the intended qualification.

As you correctly indicate, without the qualification, the function operates
on the active workbook.

 

[Jim Thomlinson]

No... thank you. The workbook argument is a nice touch that I am including in
my code archive. I have not had the need for it yet but I can shee where it
will come in handy.