Storing selection of a ListBox in an Array VBA

user_16454421 0
Viewed 184

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:

enter image description here

public vba listbox arrays
1 Answers

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