Checking to see if a worksheet exists
 

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.


--
Raman325
------------------------------------------------------------------------
Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748
View this thread: http://www.excelforum.com/showthread...hreadid=397253

Checking to see if a worksheet exists
 

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
-------------------
if IsWorksheetPresent("Week 1") then
'Do your Stuff
end i
-------------------

--
Dnere
-----------------------------------------------------------------------
Dnereb's Profile: http://www.excelforum.com/member.php...fo&userid=2618
View this thread: http://www.excelforum.com/showthread.php?threadid=39725

Checking to see if a worksheet exists
 

Great, thanks for your help

--
Raman32
-----------------------------------------------------------------------
Raman325's Profile: http://www.excelforum.com/member.php...fo&userid=2474
View this thread: http://www.excelforum.com/showthread.php?threadid=39725

Checking to see if a worksheet exists
 

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


---
Regards,
Norman



"Raman325" wrote in
message ...

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.


--
Raman325
------------------------------------------------------------------------
Raman325's Profile:
http://www.excelforum.com/member.php...o&userid=24748
View this thread: http://www.excelforum.com/showthread...hreadid=397253

Checking to see if a worksheet exists
 

"Raman325" wrote in
message ...
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

Checking to see if a worksheet exists
 

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?
--
HTH...

Jim Thomlinson


"Norman Jones" wrote:

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


---
Regards,
Norman



"Raman325" wrote in
message ...

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.


--
Raman325
------------------------------------------------------------------------
Raman325's Profile:
http://www.excelforum.com/member.php...o&userid=24748
View this thread: http://www.excelforum.com/showthread...hreadid=397253

Checking to see if a worksheet exists
 

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.


---
Regards,
Norman



"Jim Thomlinson" wrote in message
...
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?
--
HTH...

Jim Thomlinson


"Norman Jones" wrote:

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


---
Regards,
Norman



"Raman325" wrote
in
message ...

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.


--
Raman325
------------------------------------------------------------------------
Raman325's Profile:
http://www.excelforum.com/member.php...o&userid=24748
View this thread:
http://www.excelforum.com/showthread...hreadid=397253

Checking to see if a worksheet exists
 

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.
--
HTH...

Jim Thomlinson


"Norman Jones" wrote:

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.


---
Regards,
Norman



"Jim Thomlinson" wrote in message
...
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?
--
HTH...

Jim Thomlinson


"Norman Jones" wrote:

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


---
Regards,
Norman



"Raman325" wrote
in
message ...

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.


--
Raman325
------------------------------------------------------------------------
Raman325's Profile:
http://www.excelforum.com/member.php...o&userid=24748
View this thread:
http://www.excelforum.com/showthread...hreadid=397253