Determining if a Worksheet Exists
 

Excel 2003. In my VBA code I need to be able to determine if a particular
worksheet exists within a given workbook. For instance, how do I determine
if WbMaster.Worksheets(SheetName) exists without getting an error if it does
not? Thanks for the help.

--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Determining if a Worksheet Exists
 

This is one way of doing it:

Function SheetExists(ByVal strSheetName As String) As Boolean

Dim x As Object

On Error Resume Next
Set x = ActiveWorkbook.Sheets(strSheetName)

If Err = 0 Then
SheetExists = True
Else
SheetExists = False
End If

End Function

Sub Test()

If SheetExists("TestSheet") Then
Msgbox "Sheet exists"
End if

End Sub


RBS


"Chaplain Doug" wrote in message
...
Excel 2003. In my VBA code I need to be able to determine if a particular
worksheet exists within a given workbook. For instance, how do I
determine
if WbMaster.Worksheets(SheetName) exists without getting an error if it
does
not? Thanks for the help.

--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Determining if a Worksheet Exists
 

Hi Chaplain Doug,

Chaplain Doug wrote:
Excel 2003. In my VBA code I need to be able to determine if a
particular worksheet exists within a given workbook. For instance,
how do I determine if WbMaster.Worksheets(SheetName) exists without
getting an error if it does not? Thanks for the help.

Error handling is the quickest way to do this, especially if you have lots
of worksheets:

Public Function gbWorksheetExists(rsWorksheetName As String, _
Optional rwbWorkbook As Workbook = Nothing) As Boolean
On Error Resume Next

If rwbWorkbook Is Nothing Then Set rwbWorkbook _
= ActiveWorkbook
gbWorksheetExists = Len(rwbWorkbook.Worksheets( _
rsWorksheetName).Name)
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Determining if a Worksheet Exists
 

hi,

on error resume next
set ws=WbMaster.Worksheets(SheetName)
if err.number<0 then
err.clear
msbox "don't exist"
else
msbox "exist"
end if

you can make a function Exists

function Exists (sh as worksheet)
on error resume next
set ws=sh
if err.number<0 then
Exists=false
else
Exists=true
end if
end function

and call the function in a main routine

if Exists(WbMaster.Worksheets(SheetName)) then
'Do something
else
'Do something else
end if

Regards

Chaplain Doug a écrit :
Excel 2003. In my VBA code I need to be able to determine if a particular
worksheet exists within a given workbook. For instance, how do I determine
if WbMaster.Worksheets(SheetName) exists without getting an error if it does
not? Thanks for the help.