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)
rivate Su