Loading Sheets into a Customer Collection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ExcelMonkey - 26 May 2008 16:28 GMT

I have a custom collection called Myworksheets. I want to load a certain
sheets into this collection. The syntax below works. I have two questions:

1) Can someone explain the syntax to me. Not sure what the difference is
between the sheet name in the bracket and the sheet name outside the bracket.

2) This assumes I know the names of the sheets. What if the sheet names
were loaded into a listbox. How would I adjust the code to loop through the
list box and load the sheets into my custom collection.

With MyWorksheets
.Add ThisWorkbook.Worksheets("Sheet1"), "Sheet1"
.Add ThisWorkbook.Worksheets("Sheet4"), "Sheet4"
End With

Thanks

EM

Reply to this Message

Norman Jones - 26 May 2008 16:56 GMT

Hi Excel Monkey,

The instruction:

> .Add ThisWorkbook.Worksheets("Sheet1"), "Sheet1"

is an abbreviated form of:

myColl.Add Item:=ThisWorkbook.Worksheets("Sheet1"), _
Key:="Sheet1"

This loads the worksheet into the collection,
as an object (a worksheet) and uses the
worksheet's name as the Collection's key.

To load the load the Collection with the
contents of a ListBox, try something like:
'===========>>
Private Sub CommandButton1_Click()
Dim i As Long
Dim myColl As Collection

Set myColl = New Collection

On Error Resume Next
With Me.ListBox1
For i = 1 To .ListCount
myColl.Add item:=.List(i), Key:=CStr(.List(i))
Next i
On Error GoTo 0
End With
End Sub
'<<===========

---
Regards.
Norman

>I have a custom collection called Myworksheets. I want to load a certain
> sheets into this collection. The syntax below works. I have two
[quoted text clipped - 17 lines]
>
> EM

Reply to this Message

ExcelMonkey - 26 May 2008 18:24 GMT

That's helpful. Thank-you.

EM

> Hi Excel Monkey,
>
[quoted text clipped - 54 lines]
> >
> > EM

Reply to this Message