Excellent! Much more efficient. Many Many thanks!
--
Programmer on Budget


"p45cal" wrote:


Budget Programmer;477115 Wrote:
Hi P45cal. I appreciate your help very much. I coped your code and it
works
fine. But I'm back to my original problem. How do I call UserForm
FROM MY
FIRST EXECUTED MODULE (My "Driver" module) and receive back the
collection
called "x"? I tried DIM'ing "x" as "Public x as Collection" in the
UserForm,
but it that wouldn't work either. The only code is a driver module and
the
userform (There's no code in the Worksheets.
Many Thanks for all your help.
--
Programmer on Budget


This in a standard code module:Sub driver()
UserForm1.Show ' ShowModal property has to be set to True for this
userform
Dim x As New Collection
With UserForm1.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then x.Add Sheets(.List(i))
Next i
'x now has your collection of sheets
End With
blah x
Unload UserForm1
'x here still has your collection of sheets
'Set x = Nothing'later, for housekeeping
End Sub

Sub blah(shs)
For Each mysht In shs
mysht.Activate
MsgBox mysht.Name & " active now?"
Next mysht
End Sub
and this behind the userform:Private Sub
UserForm_Initialize()
For Each Sht In ThisWorkbook.Sheets
ListBox1.AddItem Sht.Name
Next Sht
End Sub
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub


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