I am trying to store the users selection from a listbox which is in a userform. I am currently using public property get to retrieve my values from the userform from another module.
This is inside my userform:
Public Property Get DateFrom() As String
DateFrom = TextBox1.Text
End Property
Public Property Get DateTo() As String
DateTo = TextBox2.Text
End Property
Public Property Get Cost() As String
Cost = TextBox3.Text
End Property
Public Property Get Expense() As String
Expense = TextBox4.Text
End Property
Which work when called from another module
Sub FormNLReport()
With New NLTrans
.Show vbModal
On Error GoTo CloseF
NLReport .DateFrom, .DateTo, .Cost, .Expense
CloseF: Unload NLTrans
Exit Sub
End With
End Sub
Which then links to this sub class:
Sub NLReport(DateFrom As String, DateTo As String, Cost As String, Expense As String)
However, I now want to add a multiselection listbox but getting confused with how this can be passed to the module from the userform. Currently I have done this:
Public Property Get Items(ByRef i As Long) As String
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Items(i) = ListBox1.List(i)
MsgBox (Items(i))
End If
Next i
End Property
My other module:
Sub FormNLReport()
With New NLTrans
.Show vbModal
On Error GoTo CloseF
NLReport .DateFrom, .DateTo, .Cost, .Expense, .Items()
CloseF: Unload NLTrans
Exit Sub
End With
End Sub
Sub NLReport(DateFrom As String, DateTo As String, Cost As String, Expense As String, Items() As String)
Then when I try to call this from the other module I get an error:
This property will return an array of the selected values:
Public Property Get Items() As String()
Dim i As Long, selected As Long
Dim selectedItems() As String
For i = 0 To ListBox1.ListCount - 1
If ListBox1.selected(i) = True Then
ReDim Preserve selectedItems(selected)
selectedItems(selected) = ListBox1.List(i)
selected = selected + 1
End If
Next i
items = selectedItems
End Property
You can pass it like this:
NLReport Me.DateFrom, Me.Items
And access it like this:
Sub NLReport(DateFrom As String, ArrayItems() As String)
MsgBox ArrayItems(0) '// assumes at lease 1 selected item
End Sub