Budget Programmer;476464 Wrote:
Hello P45cal. Thanks for the response.

Would you please clarify where the "shs" comes in? My code execution
starts
in the module.

Thanks for all your help.

CommandButton1_Click()
Dim x As New Collection
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then x.Add Sheets(ListBox1.List(i))
Next i
blah x
End Sub

Private Sub UserForm_Initialize()
For Each Sht In ThisWorkbook.Sheets
ListBox1.AddItem Sht.Name
Next Sht
End Sub

and this in a module:

Sub blah(shs)
For Each mysht In shs
mysht.Activate
MsgBox mysht.Name & " active now?"
Next mysht
End Sub

No error, all worked, each sheet that had been
selected in the multiselect listbox sheet was activated in turn.

--
Programmer on Budget


"p45cal" wrote:


I think you should be able to pass on a collection.
I had this as the code behind a userform:Private Sub
CommandButton1_Click()
Dim x As New Collection
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then x.Add Sheets(ListBox1.List(i))
Next i
**BLAH X**
End Sub

Private Sub UserForm_Initialize()
For Each Sht In ThisWorkbook.Sheets
ListBox1.AddItem Sht.Name
Next Sht
End Sub

and this in a module:

*Sub blah(shs)*
For Each mysht In shs
mysht.Activate
MsgBox mysht.Name & " active now?"
Next mysht
End SubNo error, all worked, each sheet that had been
selected in the multiselect listbox sheet was activated in turn.


--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: 'The Code Cage Forums - View Profile: p45cal'
(http://www.thecodecage.com/forumz/member.php?userid=558)
View this thread: 'Pass Object from UserForm to Code Module - The
Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=131480)



I added to the thread at thecodecage.com (http://tinyurl.com/mdwwea).
The *shs *is the identity the passed collection takes on in *blah*.
It is passed as *x* in the call to blah. See the red highlights in the
blue section of the quote above (you may have to vist thecodecage.com to
do this!).
It is a collection of worksheets.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131480