Hello,

At a certain point in my code module, I need my user to determine which of
the worksheets in the workbook need further processing. In order to
accomplish this I made a UserForm with a ListBox. It lists all the
worksheets in the workbook. The user pics the worksheets that need further
processing, which I store that in a Collection. That part seems works fine.

My problem is that I can't seem to pass the collection back to the module.
I can pass a variant OK, but I can't seem to pass a collection.

What's a good way to approach this?

Many Thanks.
--
Programmer on Budget

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 Suband 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: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131480

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 Suband 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: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131480

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

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


"p45cal" wrote:


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

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

Hello p45cal
Please notice Patrick Malloy's response below. I didn't notice his line
"set colMine = Userform1.colUser". I was trying to access the public
variable (colUser) directly. I didn't realize it had to be called as an
arguement to the UserForm. Makes perfect sense now. Many thanks for your
help. I appreciate it. I'm all set now.
--
Programmer on Budget


"p45cal" wrote:


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

Budget Programmer wrote:
Hello,

At a certain point in my code module, I need my user to determine which of
the worksheets in the workbook need further processing. In order to
accomplish this I made a UserForm with a ListBox. It lists all the
worksheets in the workbook. The user pics the worksheets that need further
processing, which I store that in a Collection. That part seems works fine.

My problem is that I can't seem to pass the collection back to the module.
I can pass a variant OK, but I can't seem to pass a collection.

What's a good way to approach this?

Many Thanks.

You can pass a collection as a parameter. E.g., this works:

'Worksheet code:
Private Sub CommandButton1_Click()
Dim myCol As New Collection
myCol.Add ("a")
myCol.Add ("c")
test myCol
End Sub

'Module code:
Sub test(acol As Collection)
Dim i As Long
For i = 1 To acol.Count
Debug.Print acol.Item(i)
Next i
End Sub

The questions for you are
- where are you building the collection
- from where are you passing the collection
- what is the scope of the collection object

Hi smartin,
1. THe collection is being built in the UserForm.
2. It's being passed from the Userform to the Module. THe module starts
everything, and it calls the userform.
3. The object will be a collection of worksheet names.

Thanks for all your help.
--
Programmer on Budget


"smartin" wrote:

Budget Programmer wrote:
Hello,

At a certain point in my code module, I need my user to determine which of
the worksheets in the workbook need further processing. In order to
accomplish this I made a UserForm with a ListBox. It lists all the
worksheets in the workbook. The user pics the worksheets that need further
processing, which I store that in a Collection. That part seems works fine.

My problem is that I can't seem to pass the collection back to the module.
I can pass a variant OK, but I can't seem to pass a collection.

What's a good way to approach this?

Many Thanks.

You can pass a collection as a parameter. E.g., this works:

'Worksheet code:
Private Sub CommandButton1_Click()
Dim myCol As New Collection
myCol.Add ("a")
myCol.Add ("c")
test myCol
End Sub

'Module code:
Sub test(acol As Collection)
Dim i As Long
For i = 1 To acol.Count
Debug.Print acol.Item(i)
Next i
End Sub

The questions for you are
- where are you building the collection
- from where are you passing the collection
- what is the scope of the collection object

If you want to call code in regular module from code within the form,
while the form remains visible, do something like

'[ In UserForm1]

Private Sub CommandButton1_Click()
Dim N As Long
Dim M As Long
Dim Arr() As String
With Me.ListBox1
ReDim Arr(1 To .ListCount)
For N = 0 To .ListCount - 1
If .Selected(N) Then
M = M + 1
Arr(M) = .List(N)
End If
Next N
End With

If M 0 Then
ReDim Preserve Arr(1 To M)
DoSelectedItems Arr
End If
End Sub

This will create an array of strings named Arr and fill it with the
selected items in ListBox1. It then calls a procedured named
DoSelectedItems passing it the array of selected items.

' [ In Module1]
Sub DoSelectedItems(Arr As Variant)
Dim N As Long
If IsArray(Arr) Then
For N = LBound(Arr) To UBound(Arr)
Debug.Print Arr(N)
Next N
Else
Debug.Print CStr(Arr)
End If
End Sub

This procedure loops through Arr (the selected list items on
UserForm1, passed by the CommandButton1_Click), and simply write the
values out to the Immediate window.

If you want to get the selected values after the UserForm has been
closed by the user, try:

' [ In UserForm1 ]
Public SelectedItems As Variant

Private Sub btnClose_Click()
Dim N As Long
Dim M As Long

With Me.ListBox1
ReDim SelectedItems(1 To .ListCount)
For N = 0 To .ListCount - 1
If .Selected(N) Then
M = M + 1
SelectedItems(M) = .List(N)
End If
Next N
End With

If M 0 Then
ReDim Preserve SelectedItems(1 To M)
End If
Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "You must user the Close button to close the form"
Cancel = True
End If
End Sub

The code in btnClose_Click loads the SelectedItems variable to an
array contain the items selected in ListBox1. It then uses Hide rather
than Unload to close the form. When you Hide a userfrom, it remains in
memory and its contents can be read. If you Unload a form, it is
dumped from memory and you cannot access its values. For this reason,
btnClose uses Hide and QueryClose cancels the close if the user
clicked on the "X" button on title bar of the form.

Then, in Module1, use something like

Sub AAA()
Dim Arr As Variant
Dim N As Long
UserForm1.Show
Arr = UserForm1.SelectedItems
If IsArray(Arr) Then
For N = LBound(Arr) To UBound(Arr)
Debug.Print N, Arr(N)
Next N
Else
Debug.Print CStr(Arr)
End If
Unload UserForm1
End Sub

This code Shows the userform. When the user clicks btnClose, the
form's code populates SelectItems with the selected items in ListBox1.
Since SelectedItems is declare Public in the form's module, it can be
read by code after the from has been hidden (but not Unloaded).

As long as the form is still loaded after it is hidden, you can simply
read its control values directly:

' [ In Module1 ]
Dim N As Long
With UserForm1
.Show
With .ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
Debug.Print .List(N)
End If
Next N
End With
End With

Yet another way is that if you already have the seletced items in a
Collection within the user form, just declare that variable as Public:

'[ In UserForm1 ]
Public MyCollection As Collection

You code can access that directly:

'[ in Module1]
For Each X In UserForm1.MyCollection
' whatever
Next X

If this code is to be called after the form is dismissed, you must
ensure that it was dismissed with Hide and not Unload. You can use the
QueryClose code above to prevent user from unloading the form.

There are other variations on these same themes.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)










On Thu, 3 Sep 2009 15:45:01 -0700, Budget Programmer
wrote:

Hello,

At a certain point in my code module, I need my user to determine which of
the worksheets in the workbook need further processing. In order to
accomplish this I made a UserForm with a ListBox. It lists all the
worksheets in the workbook. The user pics the worksheets that need further
processing, which I store that in a Collection. That part seems works fine.

My problem is that I can't seem to pass the collection back to the module.
I can pass a variant OK, but I can't seem to pass a collection.

What's a good way to approach this?

Many Thanks.

so a code module opens teh userform - the user selects some items that go
into a collection and you beed the subsequent code to use that collection.
when you DIM the collectino in the userform, make it PUBLIC and at the
starte of the code module
lets name it colUserList
when the user its an OK button? then the form gets hidden?

' your mdodule code
dim colMine
....blah...
userform1.Show
set colMine = Userform1.colUser
unload userform1
....blah your code using colMine


FYI my standard module :
Sub test()

Dim uf As UserForm1
Dim colMine As Collection
Set uf = UserForm1
uf.Show
Set colMine = uf.colUser
Unload uf
' process colMine
End Sub

my userform had one button and this code:
Option Explicit
Public colUser As Collection
Private Sub CommandButton2_Click()
Me.Hide
End Sub
Private Sub UserForm_Initialize()
Set colUser = New Collection
With colUser
.Add 1, "A"
.Add 2, "B"
.Add 3, "C"

End With
End Sub

HTH

"Budget Programmer" wrote:

Hello,

At a certain point in my code module, I need my user to determine which of
the worksheets in the workbook need further processing. In order to
accomplish this I made a UserForm with a ListBox. It lists all the
worksheets in the workbook. The user pics the worksheets that need further
processing, which I store that in a Collection. That part seems works fine.

My problem is that I can't seem to pass the collection back to the module.
I can pass a variant OK, but I can't seem to pass a collection.

What's a good way to approach this?

Many Thanks.
--
Programmer on Budget

blimey - full of typos! heres a 2nd attempt....

"Patrick Molloy" wrote:

so a code module opens the userform - the user selects some items that go
into a collection and you need. then the subsequent code to uses that
collection.
when you DIM the collection in the userform, make it PUBLIC and at the
start of the code module lets name it colUserList
when the user its an OK button? then the form gets hidden?

the following code demonstrates this - you F8 through sub Test

' your mdodule code
dim colMine
...blah...
userform1.Show
set colMine = Userform1.colUser
unload userform1
...blah your code using colMine


FYI my standard module :
Sub test()

Dim uf As UserForm1
Dim colMine As Collection
Set uf = UserForm1
uf.Show
Set colMine = uf.colUser
Unload uf
' process colMine
End Sub

my userform had one button and this code:
Option Explicit
Public colUser As Collection
Private Sub CommandButton2_Click()
Me.Hide
End Sub
Private Sub UserForm_Initialize()
Set colUser = New Collection
With colUser
.Add 1, "A"
.Add 2, "B"
.Add 3, "C"

End With
End Sub

HTH

"Budget Programmer" wrote:

Hello,

At a certain point in my code module, I need my user to determine which of
the worksheets in the workbook need further processing. In order to
accomplish this I made a UserForm with a ListBox. It lists all the
worksheets in the workbook. The user pics the worksheets that need further
processing, which I store that in a Collection. That part seems works fine.

My problem is that I can't seem to pass the collection back to the module.
I can pass a variant OK, but I can't seem to pass a collection.

What's a good way to approach this?

Many Thanks.
--
Programmer on Budget

Patrick.
I didn't notice the statement "set colMine = Userform1.colUser" That was my
problem. Your suggestion did the trick. Many Thanks. I'm all set!
--
Programmer on Budget


"Patrick Molloy" wrote:

blimey - full of typos! heres a 2nd attempt....

"Patrick Molloy" wrote:

so a code module opens the userform - the user selects some items that go
into a collection and you need. then the subsequent code to uses that
collection.
when you DIM the collection in the userform, make it PUBLIC and at the
start of the code module lets name it colUserList
when the user its an OK button? then the form gets hidden?

the following code demonstrates this - you F8 through sub Test

' your mdodule code
dim colMine
...blah...
userform1.Show
set colMine = Userform1.colUser
unload userform1
...blah your code using colMine


FYI my standard module :
Sub test()

Dim uf As UserForm1
Dim colMine As Collection
Set uf = UserForm1
uf.Show
Set colMine = uf.colUser
Unload uf
' process colMine
End Sub

my userform had one button and this code:
Option Explicit
Public colUser As Collection
Private Sub CommandButton2_Click()
Me.Hide
End Sub
Private Sub UserForm_Initialize()
Set colUser = New Collection
With colUser
.Add 1, "A"
.Add 2, "B"
.Add 3, "C"

End With
End Sub

HTH

"Budget Programmer" wrote:

Hello,

At a certain point in my code module, I need my user to determine which of
the worksheets in the workbook need further processing. In order to
accomplish this I made a UserForm with a ListBox. It lists all the
worksheets in the workbook. The user pics the worksheets that need further
processing, which I store that in a Collection. That part seems works fine.

My problem is that I can't seem to pass the collection back to the module.
I can pass a variant OK, but I can't seem to pass a collection.

What's a good way to approach this?

Many Thanks.
--
Programmer on Budget