Check for an item in a collection (without looping)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
================================
 
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
 
Back
Top