How to correctly retrieve each selected value from a multiselect listbox array (VBA)

Question

I have a multiselect listbox that is populated with concatenated values (consisting of a loaned item's item ID no. and item name) when a cbo box is updated (loaner's name selected).

Essentially, I would like to be able to recall, or refer to, the first concatenated value of each list item selected. The following code is my attempt at saving values of the listbox in an array whilst trying to incorporate the recalling of only the first concatenated value of each selection.

This code functions, however for some reason it only recalls the last selected value. For example, if the following list box items are selected; "1:Anthropometric Tape Measure" and "2:Anthropometric Measuring Kit" then the values reported (in a msgbox for troubleshooting purposes) are "2,2" IF the second item is selected last, instead of "1,2".

What is strange is that if I use the array code without referring to only the first concatenated value, (tmpArray(selCount) = lbox.List(i) instead of Split(lbox.List(lbox.ListIndex), ":")(0)) the code functions correctly.

Any advice on fixing this would be extremely appreciated, thanks!

Public Function GetSelectedItems(lbox As Object)

Dim tmpArray() As Variant
Dim i As Integer
Dim selCount As Integer
    selCount = -1

    For i = 0 To lbox.ListCount - 1

        If lbox.Selected(i) = True Then

            selCount = selCount + 1
            ReDim Preserve tmpArray(selCount)
            tmpArray(selCount) = Split(lbox.List(lbox.ListIndex), ":")(0)
        End If
    Next

    If selCount = -1 Then

        GetSelectedItems = ""
    Else:
        GetSelectedItems = Join(tmpArray, ", ")
    End If

End Function

I am using the following code to recall the array into the message box (FYI).

Dim mySentence As String

mySentence = GetSelectedItems(Me.ListItem)

  Msg = "You selected" & vbNewLine & mySentence

Mikku Mikku · Accepted Answer · 2019-07-29T03:45:00

Problem:

Use of Lbox.ListIndex

Answer:

Instead have to use i. Above refereed to the selected Item in Single Selection ListBox. i will give the correct Index of the Selected Item in Multiple Selection.

Code:

Public Function GetSelectedItems(lbox As Object)

Dim tmpArray() As Variant
Dim i As Integer
Dim selCount As Integer
    selCount = -1

    For i = 0 To lbox.ListCount - 1

        If lbox.Selected(i) = True Then

            selCount = selCount + 1
            ReDim Preserve tmpArray(selCount)
            tmpArray(selCount) = Split(lbox.list(i), ":")(0)

            Set fnd = Worksheets("Sheet7").Range("B11:B19").Find(Split(lbox.list(i), ":")(0))

            If Not fnd Is Nothing Then

                  fnd.Offset(0, 1).Value = "Selected"

            End If


        End If
    Next

    If selCount = -1 Then

        GetSelectedItems = ""
    Else:
        GetSelectedItems = Join(tmpArray, ", ")
    End If

End Function

Change the Worksheet Name & Range

Demo:

How to correctly retrieve each selected value from a multiselect listbox array (VBA)

1 Answers