Is it possible to create a custom worksheet collection in VBA. That is I
normally loop thru worksheets as follows:

For Each wrksht In ActiveWorkbook

Next

But what if I want to loop thru certain sheets which I will define by items
in listboxes chosen by the user. I know I can create a collection and add
items to it but I am not sure how access that collection using the For Loop
above.

Does this mean I have to build a class module to create a custom worksheet
class?

Any ideas?

Thanks

EM

On Jan 27, 7:12 pm, ExcelMonkey
<ExcelMon...@discussions.microsoft.com> wrote:
> Is it possible to create a custom worksheet collection in VBA. That is I
> normally loop thru worksheets as follows:
>
> For Each wrksht In ActiveWorkbook
>
> Next
>
> But what if I want to loop thru certain sheets which I will define by items
> in listboxes chosen by the user. I know I can create a collection and add
> items to it but I am not sure how access that collection using the For Loop
> above.
>
> Does this mean I have to build a class module to create a custom worksheet
> class?
>
> Any ideas?
>
> Thanks
>
> EM

Hello Excel Monkey,

You don't need to create a Class Module for this. In this example, all
the code is in a Standard VBA Module. To make the collection available
throughout the project, the collection object variable is declared as
public. The LoadMyWorksheets macro puts the sheets you want into your
collection. You can then reference them using this format ...
MyWorksheets("sheet name")
This example loads 2 worksheets each from different workbooks.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Module Code
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Public MyWorksheets As Object

Sub LoadMyWorksheets()

Set MyWorksheets = New Collection

With MyWorksheets
.Add ThisWorkbook.Worksheets("Sheet1"), "Sheet1"
.Add Workbooks("Code For Lookup.xls").Worksheets("Sheet4"),
"Sheet4"
End With

End Sub
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Example
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub ReadData()

X = MyWorksheets("Sheet4").Range("A1").Value

End Sub
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sincerely,
Leith Ross