MS Access: Multi ListBox get values

0
votes

I have a listbox on a form that contains 6 values. The user is allowed to select multiple values at once. For each value selected I want to assign this value to a variable.

My listbox is called: lstFilterUnits

The listbox contents are as follows: Blue, Red, Green, Yellow, Orange, White

I understand that the following is associated with each colour:

Forms("Form1").lstFilterUnits.Selected(0) 'Blue Selected
Forms("Form1").lstFilterUnits.Selected(1) 'Red Selected
Forms("Form1").lstFilterUnits.Selected(2) 'Green Selected
Forms("Form1").lstFilterUnits.Selected(3) 'Yellow Selected
Forms("Form1").lstFilterUnits.Selected(4) 'Orange Selected
Forms("Form1").lstFilterUnits.Selected(5) 'White Selected

I have attempted to do this using the below code in the listbox on click event:

As you can see I had to hard code the value assigned to the vUnit variable as anytime I try get the value associated with the list box with either of the lines commented out it doesn't work.

Dim vUnit As String

If Forms("Form1").lstFilterUnits.Selected(0) = True Then

    vUnit = "Blue
    MsgBox (vUnit)
    'MsgBox "You selected " & lstFilterUnits.Value 'Returns null value
    'MsgBox "You selected " & lstFilterBusinessUnits.SelectedValue 'Returns Compile error
    
ElseIf Forms("Form1").lstFilterUnits.Selected(1) = True Then

    vUnit = vUnit  & ", Red'"
    MsgBox (vUnit)

End If

So for the above code I can't seem to assign the value associated with the selected item to a variable. I also want to me able to display multiple values in the variable if they are selected, however if I select more than one value using the above method it will only go into the first if statement that is true, due to this I think I should be using some sort of a loop to assign the variable multiple values but I'm new to VBA and d not know how to do this.

Any help would be appreciated, I think this is probably a simple task that I am overthinking.

EDIT:

After searching online I finally found something that is kind of doing what I need. However the output is also outputting the index number of the listbox. The code I found is below. I've tried playing around with it but can't seem to achieve my desired output.

The below code will output the following:

'1 , 'Blue', '2 'Red', 3 ,'Green',

Whereas I want it to output:

'Blue','Red', 'Green',

Does anyone know how I can achieve this? I've tried changing the column to both only i and J and this didn't seem to work. I've never worked with arrays in VBA before so will admit I don't understand the code fully and the form I found it on didn't explain the code, it was just providing a solution for another user.

Dim i As Long
Dim J As Long
Dim Msg As String
Dim arrItems() As String

    ReDim arrItems(0 To lstFilterUnits.ColumnCount - 1)
    For J = 0 To lstFilterUnits.ListCount - 1
        If lstFilterUnits.Selected(J) Then

            For i = 0 To lstFilterUnits.ColumnCount - 1
                arrItems(i) = lstFilterUnits.Column(i, J)
            Next i
            Msg = Msg & "'" & Join(arrItems, " , '") & "', "

        End If
    Next J
    MsgBox Msg
2
vbams-access

2 Answers

1
votes

Have a try on it.

Private Sub lstFilterUnits_Click()
Dim ctl As Control
Dim varItm As Variant
Dim SelectedColor As String

Set ctl = Forms!Form1!lstFilterUnits
    
    For Each varItm In ctl.ItemsSelected
        SelectedColor = SelectedColor & vbCrLf & ctl.ItemData(varItm) 'This for each color in each line
        'SelectedColor = SelectedColor & "," & ctl.ItemData(varItm) ' This line for comma separated color
    Next varItm

MsgBox SelectedColor
End Sub
2
votes

Use the .ItemsSelected collection. It already has all the items.

EXAMPLE:

Sub Example() 
    dim ctl as Control
    Set ctl = Forms("Form1").lstFilterUnits
    Dim varItm As Variant 
    For Each varItm In ctl.ItemsSelected 
        Debug.Print ctl.ItemData(varItm) 
    Next varItm 
End Sub

SOURCE: https://docs.microsoft.com/en-us/office/vba/api/access.listbox.itemsselected