Let's say I have sheets 1 to 10, and I add some of those sheets to a
collection. I want to be able to test whether a given sheet (say sheet8) is
a member of that collection. I know I could test each item in the collection
using a loop, but I want to avoid that if posible. Any help??

set SheetsCollection = sheets(Array("Sheet1", "Sheet7", "Sheet4"))

For each sht in Activeworkbook.sheets
' If sht is in SheetsCollection then
msgbox "Sheet found in collection"
' else
msgbox "Sheet not Found in collection"
' end if
Next

If I understood correctly why not this sub

Public Sub test()
On Error GoTo line1
Worksheets("sheet8").Activate
GoTo line2
line1:
MsgBox "sheet8 is not available"
line2:
End Sub
================================


Rob wrote in message
...
Let's say I have sheets 1 to 10, and I add some of those sheets to a
collection. I want to be able to test whether a given sheet (say sheet8)
is
a member of that collection. I know I could test each item in the
collection
using a loop, but I want to avoid that if posible. Any help??

set SheetsCollection = sheets(Array("Sheet1", "Sheet7", "Sheet4"))

For each sht in Activeworkbook.sheets
' If sht is in SheetsCollection then
msgbox "Sheet found in collection"
' else
msgbox "Sheet not Found in collection"
' end if
Next

If I follow what you're trying to do, this worked for me:

Set SheetsCollection = Sheets(Array("Sheet1", "Sheet3"))
shtName = "Sheet2" ' is not found
' shtName = "Sheet3" ' is found

On Error Resume Next
Set sht = SheetsCollection(shtName)
If Err.Number Then
Err.Clear
MsgBox "Sheet not Found in collection"
Else
MsgBox "Sheet found in collection"
End If
on error goto 0

Regards,
Peter T

"Rob" wrote in message
...
Let's say I have sheets 1 to 10, and I add some of those sheets to a
collection. I want to be able to test whether a given sheet (say sheet8)
is
a member of that collection. I know I could test each item in the
collection
using a loop, but I want to avoid that if posible. Any help??

set SheetsCollection = sheets(Array("Sheet1", "Sheet7", "Sheet4"))

For each sht in Activeworkbook.sheets
' If sht is in SheetsCollection then
msgbox "Sheet found in collection"
' else
msgbox "Sheet not Found in collection"
' end if
Next