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)
sPrompt = "No selection"
MsgBox sPrompt, vbOKOnly, sTitle
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.