VBA UserForm CommandButton ListBox Full Example

VBA & macros · Updated November 22, 2026

The complete answer: a VBA UserForm with a CommandButton and a ListBox is built by adding a ListBox, a CommandButton, and a TextBox to the form, then writing a short macro behind the button to transfer the selected ListBox row into the TextBox. Below is a full, working example that you can copy into your own project, with explanations of every part.

What the Example Does

The example creates a small data-entry form. It has:

  • A ListBox named ListBox1 showing a list of products.
  • A CommandButton named CommandButton1 labeled “Show Selected”.
  • A TextBox named TextBox1 that displays the selected product.

When the user clicks the button, the macro reads the selected item from the ListBox and writes it to the TextBox. This is the most common pattern: a button that acts on the ListBox selection.

Step-by-Step: Building the UserForm

  1. Open the VBA editor (Alt+F11 on Windows, Option+F11 on Mac).
  2. In the Project Explorer, right-click your workbook name, choose Insert > UserForm.
  3. From the Toolbox, drag a ListBox, a CommandButton, and a TextBox onto the form.
  4. Select the CommandButton, and in the Properties window set Caption to “Show Selected”.
  5. Double-click the CommandButton – this opens the code window with an empty CommandButton1_Click procedure.

You now have the form layout. The next step is to fill the ListBox and write the button’s code.

Filling the ListBox on Form Initialize

The ListBox can be filled in two ways: at design time (by setting the RowSource property) or at runtime using VBA. The runtime method is more flexible because you can populate it from any data source, such as a worksheet range or an array.

Add this code to the UserForm’s code module (double-click the form background to open it):

Private Sub UserForm_Initialize()
    ' Populate the ListBox with a few product names
    ListBox1.AddItem "Laptop"
    ListBox1.AddItem "Mouse"
    ListBox1.AddItem "Keyboard"
    ListBox1.AddItem "Monitor"
End Sub

This runs every time the form is loaded. If you want to load from a worksheet, you can replace the AddItem lines with a loop over a range. For example, with product names in cells A1:A10 on “Sheet1”:

Dim i As Long
For i = 1 To 10
    ListBox1.AddItem ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value
Next i

The CommandButton Click Code

Now write the button’s click event. The key is to check whether the user has actually selected a row – if not, you show a message or do nothing. Here is the full code for the button:

Private Sub CommandButton1_Click()
    ' Check if a selection exists
    If ListBox1.ListIndex < 0 Then
        MsgBox "Please select an item first.", vbExclamation
        Exit Sub
    End If
    
    ' Write the selected item to the TextBox
    TextBox1.Value = ListBox1.Value
End Sub

Explanation:

  • ListBox1.ListIndex returns the index of the selected row, or -1 if nothing is selected.
  • ListBox1.Value returns the text of the selected row (if the ListBox is single-column).
  • The MsgBox is optional but improves usability.

To show the form, you need a macro that displays it. For example, from a worksheet button or a regular module:

Sub ShowMyForm()
    UserForm1.Show
End Sub

Remember to save a copy of your workbook before running any macro, because VBA actions cannot be undone with Ctrl+Z.

Handling Multi-Column ListBoxes

Often you want a ListBox with multiple columns, like ID and Name. To do that, set the ColumnCount property of the ListBox to 2 (or more) and add items with multiple values separated by tabs. For example:

ListBox1.ColumnCount = 2
ListBox1.AddItem "P001" & vbTab & "Laptop"
ListBox1.AddItem "P002" & vbTab & "Mouse"

When you retrieve the selection, ListBox1.Value returns only the first column. To get the second column, use ListBox1.List(ListBox1.ListIndex, 1) (column index starts at 0). So the button code would be:

TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 1)

This is a common source of confusion – many users expect .Value to return all columns, but it does not.

Common Problems and Fixes

  • ListBox appears empty: Make sure the UserForm_Initialize code is in the UserForm’s code module, not a standard module. Also check that the form is actually showing (call UserForm1.Show).
  • Button does nothing: Double-check that the click procedure is named CommandButton1_Click and that the button’s name is indeed CommandButton1. If you renamed the button, the event name changes.
  • “Object doesn’t support this property or method”: This often happens when you try to use .Value on a ListBox that has MultiSelect = fmMultiSelectMulti. In multi-select mode, you must loop through ListBox1.Selected(i) to find all selected items.
  • ListBox not updating after changing data: You need to clear and re-add items. Use ListBox1.Clear before re-populating.

Multi-Select ListBox Example

If you set MultiSelect = fmMultiSelectMulti (or fmMultiSelectExtended), the user can select multiple rows. The button code must then loop through all items:

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim result As String
    
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            result = result & ListBox1.List(i) & vbCrLf
        End If
    Next i
    
    If result = "" Then
        MsgBox "No selection."
    Else
        TextBox1.Value = result
    End If
End Sub

This builds a string with each selected item on a new line. Note that ListCount gives the number of rows, and List(i) gives the value of row i (0-based).

Frequently Asked Questions

How do I add items to a ListBox from a worksheet range?

Use a loop as shown earlier, or set the RowSource property to a range address like Sheet1!A1:A10. But RowSource is static and does not update if the range changes, so a loop is often better for dynamic data.

How do I get the selected item’s text in a multi-column ListBox?

Use ListBox1.List(ListBox1.ListIndex, columnIndex) where columnIndex is 0 for the first column, 1 for the second, etc. For multi-select, loop through Selected(i) and use List(i, columnIndex).

Why does my UserForm not show when I press F5?

Pressing F5 runs the current macro. If you are in the UserForm code module, F5 may run the form’s Initialize event, not show the form. You need to run a macro that calls UserForm1.Show. Create a standard module with a Sub that shows the form.

Can I use a ListBox to edit data in a worksheet?

Yes, you can write the selected value back to a cell. For example, Range("A1").Value = ListBox1.Value inside the button click. But be careful: if the user changes the selection, you may want to update the cell on each click, not automatically.

What is the difference between ListIndex and Value?

ListIndex is the row number (0-based) of the selected item, or -1 if nothing is selected. Value is the actual text of the selected row (first column only). Use ListIndex to check if a selection exists, and Value or List to get the content.

How do I close the UserForm when the button is clicked?

Use Unload Me inside the button’s click event. For example, after writing to the TextBox, you could add Unload Me to close the form.

Final Notes

This example gives you a solid foundation. You can extend it by adding more buttons (like “Add”, “Delete”), using a ComboBox instead of a ListBox, or linking to a database. The key is to understand the event model: the UserForm’s Initialize event runs when the form loads, and the button’s Click event runs when the user clicks. Always test your code on a copy of your workbook, and remember that VBA macros cannot be undone with Ctrl+Z.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.