Retrieve Values From a Multi-Column/Select ListBox
That’s a lot of ‘Multi’s’. Friday, I blogged about getting data from a multi-column listbox. If the listbox is multi-select also, you need a little different technique.
First, loop through all the items in the listbox and check the Selected property. If it’s True, then use the Column property and identify the column and row you want. The row will be the loop counter variable. You can’t use the ListIndex property with a multi-select, so check the length of the string variable at the end to see if nothing is selected.
Private Sub CommandButton1_Click()
Dim i As Long
Dim sPrompt As String
Dim sTitle As String
sTitle = “You selected…”
With Me.ListBox1
For i = 0 To .ListCount – 1
If .Selected(i) Then
sPrompt = sPrompt & .Column(0, i) & vbTab & .Column(1, i) & vbNewLine
End If
Next i
End With
If Len(sPrompt) = 0 Then sPrompt = “Nothing Selected”
MsgBox sPrompt, vbOKOnly, sTitle
End Sub
The .Column was what I was looking for. I had a multiselect list box with 4 columns. My array was only capturing the 1st column and I need all 4. I could not figure out how to capture the selected items (all 4 columns) into an array. Now I need to search on how to write the array to a selected range (preferably using on selected cell and offset from there). Thanks for the great push in the right direction.