[VBA] How to Get Selected Values from a ListBox (Multi-Column)

Introduction

Getting data from a “selected row” after a user chooses an item in a UserForm ListBox is one of the most basic operations in VBA tool development. This is especially true for multi-column ListBoxes, where you often need to accurately retrieve data from a specific column (e.g., the product name in the second column) of the selected row.

In this article, I will explain a reliable and easy-to-understand method for getting the index number of the row selected by the user and retrieving the data for each column in that row.

VBA Sample Code to Get Selected Items

This macro checks the item selected in the ListBox when the “Get Data” button is clicked and displays its content in a message box.

It assumes that a multi-column ListBox has already been populated with data (e.g., from a sheet table) during the UserForm’s initialization event.

UserForm Code

' Process when the "Get Data" button (GetDataButton) is clicked
Private Sub GetDataButton_Click()

    ' == Define variables ==
    Dim selectedRowIndex As Long
    Dim messageText As String
    
    ' Specify the target ListBox (ProductListBox) using a With block
    With Me.ProductListBox
    
        ' Get the index of the selected row using the .ListIndex property
        selectedRowIndex = .ListIndex
        
        ' == Check if nothing is selected ==
        ' .ListIndex returns -1 if nothing is selected
        If selectedRowIndex = -1 Then
            MsgBox "No item is selected.", vbExclamation
            Exit Sub
        End If
        
        ' == Get data from each column of the selected row ==
        messageText = "[Selected Product Information]" & vbCrLf & vbCrLf & _
                      "Product ID: " & .List(selectedRowIndex, 0) & vbCrLf & _
                      "Product Name: " & .List(selectedRowIndex, 1) & vbCrLf & _
                      "Price: " & .List(selectedRowIndex, 2) & " Yen"
    
    End With
    
    ' == Display the result in a message box ==
    MsgBox messageText, vbInformation, "Confirmation of Selection"

End Sub

Explanation of the Code

selectedRowIndex = .ListIndex

This single line is the first step to getting the user’s selection.

• .ListIndex: This property returns the index number of the currently selected item in the ListBox.
• Note that this index starts from 0. If the first row of the list is selected, it returns 0; if the second row is selected, it returns 1.
• If the user has not selected anything, .ListIndex returns -1. Therefore, the subsequent check If selectedRowIndex = -1 Then reliably verifies whether an item is selected.

.List(selectedRowIndex, 0)

This is the core part for retrieving specific data from the selected row.

• .List: This property allows direct access to data at a specific position in the list by specifying arguments.
• .List(RowIndex, ColumnIndex): You specify two arguments.
  • 1st Argument: The row index number (the selectedRowIndex we just retrieved).
  • 2nd Argument: The column index number (this also starts from 0; the 1st column is 0, the 2nd column is 1…).
• .List(selectedRowIndex, 0) means “the data in the 1st column of the row selected by the user.”

Summary

In this article, I explained how to retrieve data from an item selected by the user in a UserForm ListBox.

• Use the .ListIndex property to get the index number (0-based) of the selected row.
• Check that .ListIndex is not -1 to ensure an item is selected.
• Use .List(RowIndex, ColumnIndex) to pinpoint and retrieve data from the desired cell.

By mastering this procedure, you can implement various interactive processes, such as displaying detailed information or performing calculations based on the user’s selection.