Loading Sheets into a Customer Collection

[ExcelMonkey]

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

 

[Norman Jones]

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
'<<===========

 

[ExcelMonkey]

That's helpful. Thank-you.

EM

Hi Excel Monkey,

The instruction:

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
'<<===========