Determining if a Worksheet Exists

[Chaplain Doug]

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

[RB Smissaert]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Jake Marx]

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]

[anonymousA]

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