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
ListBox1showing a list of products. - A CommandButton named
CommandButton1labeled “Show Selected”. - A TextBox named
TextBox1that 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
- Open the VBA editor (Alt+F11 on Windows, Option+F11 on Mac).
- In the Project Explorer, right-click your workbook name, choose Insert > UserForm.
- From the Toolbox, drag a ListBox, a CommandButton, and a TextBox onto the form.
- Select the CommandButton, and in the Properties window set
Captionto “Show Selected”. - Double-click the CommandButton – this opens the code window with an empty
CommandButton1_Clickprocedure.
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.ListIndexreturns the index of the selected row, or -1 if nothing is selected.ListBox1.Valuereturns the text of the selected row (if the ListBox is single-column).- The
MsgBoxis 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_Initializecode is in the UserForm’s code module, not a standard module. Also check that the form is actually showing (callUserForm1.Show). - Button does nothing: Double-check that the click procedure is named
CommandButton1_Clickand that the button’s name is indeedCommandButton1. 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
.Valueon a ListBox that hasMultiSelect = fmMultiSelectMulti. In multi-select mode, you must loop throughListBox1.Selected(i)to find all selected items. - ListBox not updating after changing data: You need to clear and re-add items. Use
ListBox1.Clearbefore 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
- Excel Template for Income Expense Tracking: How to Build One
- Excel Template for Family Budget: How to Build One
- Excel Template for HOA Budget: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Get Current Workbook Name: Code and TipsTo get the name of the workbook currently active in Excel VBA, use ActiveWorkbook.Name. For example, assign Set…
- Filter a Dictionary in Excel VBA: Practical ExamplesExcel VBA has no built-in Filter method for a Scripting.Dictionary. The usual approach is to loop through the…
- Select to Bottom of Array in Excel VBA: Safe MethodsTo select from the active cell to the bottom of its contiguous data block in Excel VBA, use…
- Excel VBA Text Color: Change Font and Cell Colors EasilyTo change text color in Excel VBA, set a range’s Font.Color property, usually with RGB(red, green, blue). For…
- Excel VBA Active Worksheet: How It Works and When to Use ItIn Excel VBA, ActiveSheet refers to the sheet currently selected in the active workbook. It is not a…
- Excel VBA Find Value in Range: Examples and TipsTo find a value in an Excel range with VBA, use the range’s Find method, then check whether…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.