In the Excel object model we have the built-in collections Worksheets
and Sheets.
I like to create a custom collection of worksheets which will contain
only a sub set of these collections.
Can someone help me how I have to declare such a custom collection and
how I can add sheets to it.

I figured it out myself.

Sub WorksheetCollectioTest()

Dim wslist As New Collection

wslist.Add Worksheets("Sheet1")
wslist.Add Worksheets("Sheet2")

Dim ws As Worksheet

For Each ws In wslist
Debug.Print ws.Name
Next ws

End Sub

Is it somehow possible to create a specific Worksheets collection
rather than a generic collection in order to get access to built-in
methods and properties fro the worksheets collection?

In excel 2007 this code creates an error 13 (type mismatch) for the
set command.

Dim wsColl As Worksheets
Set wsColl = ActiveWorkbook.Worksheets

Anyone an idea how I can create a custom Worksheets collection with
the same methods as the built-in Worksheets collection?

Worksheets is the collection of worksheets in the active workbook, so using...
Worksheets(3) gives you access to the properties and methods of the third sheet in the workbook.

If you want to declare an object then...
Dim wsColl as Sheets
Set wsColl = Worksheets
wsColl(3) ' same as Worksheets(3)

If you want only specific worksheets then...
Dim wsColl As Sheets
Set wsColl = Worksheets(Array("Sheet1", "Sheet2", "Sheet4"))
(Note that here, wsColl(3) is Sheet4 - which could be anywhere in the workbook)

Also, see ActiveWindow.SelectedSheets
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)




"minimaster"
wrote in message
...
Is it somehow possible to create a specific Worksheets collection
rather than a generic collection in order to get access to built-in
methods and properties fro the worksheets collection?

In excel 2007 this code creates an error 13 (type mismatch) for the
set command.

Dim wsColl As Worksheets
Set wsColl = ActiveWorkbook.Worksheets

Anyone an idea how I can create a custom Worksheets collection with
the same methods as the built-in Worksheets collection?

Correction...
Worksheets(3) gives you access to the properties and methods of the third sheet in the workbook.
-should read-
Worksheets(3) gives you access to the properties and methods of the third worksheet in the
workbook.
---
Jim Cone

Thx a lot. Declaring the object wsColl "as Sheets" does the trick.
Is it a bug that I can't declare it "as Worksheets" collection?