Ken, an admitted Access user, needs to get data out of a MultiColumn ListBox. To get the data out, I generally use the Column property. The ListBox has a BoundColumn property. The bound column’s value can be retrieved easily with the Value property of the ListBox. For other columns, you simply specify the row and column of the value you want. Remember that rows and columns start with zero, not one.
In this example, the command button displays the first and second column of whichever row is selected by the user. The ListIndex property tells us which row is selected. I uses the Value property to get the bound column (1 in this case) and the Column property to get the second column.
Private Sub CommandButton1_Click()
Dim sPrompt As String
Dim sTitle As String
If Me.ListBox1.ListIndex > -1 Then
sTitle = "You selected..."
sPrompt = Me.ListBox1.Value 'get the bound column
sPrompt = sPrompt & " AND "
'get the second column
sPrompt = sPrompt & Me.ListBox1.Column(1, Me.ListBox1.ListIndex)
Else
sPrompt = "No selection"
End If
MsgBox sPrompt, vbOKOnly, sTitle
End Sub
Of course I would never omit the error check that makes sure something is selected – no, not me. The resulting message box is displayed below.
I should note that it is not necessary to define the row when using the column property. That line could be written as
sPrompt = sPrompt & Me.ListBox1.Column(1)
I always seem to include it though.
Thanks for the question, Ken.
Does this work if you can multi-select from your ListBox?
Murray: It should work, but you probably won’t get the results you want. You need to loop through all the entries and check their Selected property. Then instead of Column(1,ListIndex), you’d use Column(1,LoopVariable). I’ll blog this Monday with more details.
Is there an easy way to find the cell reference? For instance, if the value ‘fourth’ is in cell A4?
This was exactly what I was looking for.
-Thanks!
10 Years have passed after this post and it still helped me after a total of 6 hours of searching.
This was the most transparent explanation of all.
Thanks!
After struggling with the same problem in MS Access for weeks I applied the VBA code in Access and it worked, fantastic!