Wow...I'm a total newb for this class module stuff! Thanks for your help,
Chip. Yes, I understand the necessary changes needed for the "Attribute"
edits and have implemented them. I'll work your example into my code and
keep experimenting -- I'm pretty good at that.

I think I may be in over my head except for the most basic of class modules.
Looks like I'm gonna need to self-edumicate myself on this subject. I can
see the potential behind learning this concept, just need to pound it into
my old head :-)

Thanks again and have a good weekend,
--
Toby Erkson
http://www.bidata.net/

"Chip Pearson" wrote in message
...
Toby,

Here's some example code that works (I tested it):

In a class module named CMySheet:

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''
Option Explicit

Private Coll As Collection

Public Sub Add(WhatSheet As Worksheet)
On Error Resume Next 'ignore duplicate entries
Coll.Add Item:=WhatSheet, key:=WhatSheet.Name
End Sub


Public Property Get Item(Ndx As Integer) As Worksheet
Set Item = Coll(Ndx)
End Property

Public Property Get Count() As Integer
Count = Coll.Count
End Property


Private Sub Class_Initialize()
Set Coll = New Collection
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''

Now, you MUST (!) export the class module, open it in NotePad, and add the
Attribute statement. You don't add the Attribute statement in the VBA
editor (nor will it appear in the editor). Use NotePad to add the
statement.

In NotePad, your Item property should look like

Public Property Get Item(Ndx As Integer) As Worksheet
Attribute Item.VB_UserMemId = 0
Set Item = Coll(Ndx)
End Property

This is not an optional step. Once you've modified the class in NotePad,
import it back into VBA.

Then, in a standard code module, use code like

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''
Option Explicit
Public MySheets As CMySheets

Sub AAA()
Dim WS As Worksheet
Dim Ndx As Integer

If MySheets Is Nothing Then
Set MySheets = New CMySheets
End If

For Each WS In ThisWorkbook.Worksheets
MySheets.Add WhatSheet:=WS
Next WS

For Ndx = 1 To MySheets.Count
MsgBox MySheets(Ndx).Name
Next Ndx
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com