How to get selected value in multicolumn listbox

Yiao SUN picture Yiao SUN · Aug 31, 2016 · Viewed 51.8k times · Source

I have a multicolumn listbox in my userform and I would like to get all the values of the elements which are in the selected row in the listbox.

Here is my userform:UserForm with ListBox


Just like in the photo, I want to select one line then I will click button Associer and I could get the information of this row. I can just get the first column which is CAN20168301436 I want to get the information from the whole line.
How can I do it?
Here is my button clicked event:

Private Sub CommandButton3_Click()
   a = ListBoxResultatFind.Text
End Sub

Answer

user3598756 picture user3598756 · Aug 31, 2016

you can use this code

Private Sub CommandButton3_Click()
    Dim strng As String
    Dim lCol As Long, lRow As Long

    With Me.ListBox1 '<--| refer to your listbox: change "ListBox1" with your actual listbox name
        For lRow = 0 To .ListCount - 1 '<--| loop through listbox rows
            If .selected(lRow) Then '<--| if current row selected
                For lCol = 0 To .ColumnCount - 1 '<--| loop through listbox columns
                    strng = strng & .List(lRow, lCol) & " | " '<--| build your output string
                Next lCol
                MsgBox "you selected" & vbCrLf & Left(strng, (Len(strng) - 1)) '<--| show output string (after removing its last character ("|"))
                Exit For '<-_| exit loop
            End If
        Next lRow
    End With
End Sub