Missing Worksheet

[Lionel Fridjhon]

I have a number of workbooks with up to 200 worksheets.
The required worksheet is activated by the statement
Sheets(strVendorID).activate.
If the worksheet does not exist, I get an error.
How can I establish whether the worksheet exists before
trying to activate it, and thus take other action?

Lionel

 

[Tom Ogilvy]

On Error Resume Next
Sheets(strVendorID).activate
On Error goto 0
if lcase(activeSheet.Name) <> lcase(strVendorId) then
msgbox "sheet doesn't exist"
End if

 

[Frank Kabel]

Hi
try something like
sub foo()
dim wks as worksheet
on error resume next
set wks = worksheets(strVendorID)
if err.number<>0 then
msgbox "sheet does not exist"
else
wks.activate
end if
on error goto 0
end sub

 

[Edward Ulle]

I have been unable to find a method that determines if a particular
sheet exist so I wrote the following macro.


Public Function SheetExists(strSheetName As String) As Boolean

Dim wsWorksheet As Worksheet
Dim cChart As Chart

For Each wsWorksheet In Worksheets
If wsWorksheet.Name = strSheetName Then
SheetExists = True
Exit Function
End If
Next

For Each cChart In Charts
If cChart.Name = strSheetName Then
SheetExists = True
Exit Function
End If
Next

SheetExists = False

End Function