To use a ComboBox in an Excel VBA UserForm, add the control in the Visual Basic Editor, fill it with choices when the form opens, and read its selected value in a button’s Click event. A ComboBox lets someone choose an item from a list, and it can also allow typed entries if you configure it that way. The steps below are for Microsoft 365 Excel on Windows and use a department list as a working example.
What You’ll Need
- Excel desktop for Windows. Excel for the web cannot create or run VBA UserForms. Excel for Mac has VBA support, but its UserForm controls and editor experience can differ from Windows.
- A workbook saved as an Excel Macro-Enabled Workbook (*.xlsm), since a standard .xlsx file cannot store VBA code.
- The Developer tab available in Excel. If it is hidden, enable it through File > Options > Customize Ribbon, then select Developer.
- A worksheet named Data if you want to use the example save button as written. It will put the selection in cell A2.
Step-by-Step
- Save a macro-enabled copy and open the VBA editor. Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Then press Alt+F11 on Windows to open the Visual Basic Editor. On a Mac, use Tools > Macro > Visual Basic Editor where available, or the assigned Visual Basic Editor shortcut. Save a separate copy before running a macro: VBA changes cannot be reversed with Ctrl+Z.
- Insert a UserForm. In the editor, select your workbook project in Project Explorer. If Project Explorer is hidden, press Ctrl+R. Choose Insert > UserForm. Excel adds a blank form and opens the Toolbox. If the Toolbox is not visible, choose View > Toolbox. Click the form background and use the Properties window to change its Caption to something descriptive, such as Choose a department. The Caption is the text shown at the top of the form; it is not the form’s VBA name.
- Add and name the ComboBox. In the Toolbox, select ComboBox and click or drag on the form to place it. With the control selected, find its (Name) property in the Properties window and set it to
cboDepartment. Set its Style property to2 - fmStyleDropDownListif users must choose an item rather than type anything they want. You can also leave the default editable style if free-form entries are appropriate. Use a clear name without spaces; VBA code refers to that name. - Add a button for submitting the choice. Select CommandButton in the Toolbox and place it on the form. Set its (Name) property to
cmdSaveand its Caption to Save. A Cancel button is optional. Naming controls now makes the event procedures easier to recognize and reduces the chance of accidentally writing code against a generic name such as CommandButton1. - Load the list when the form opens. Double-click an empty part of the UserForm to open its code window. Choose UserForm from the left event dropdown and Initialize from the right dropdown if the procedure is not inserted automatically. Add this code, changing the control name or choices to suit your form:
Private Sub UserForm_Initialize()
' Fill the ComboBox when the form is created.
With Me.cboDepartment
.Clear
.Style = fmStyleDropDownList
.AddItem "Sales"
.AddItem "Finance"
.AddItem "Operations"
.AddItem "Support"
End With
End Sub
The Initialize event runs as the form is loaded, so the choices are ready before the user opens the list. The .Clear line prevents duplicate entries if the initialization routine is called more than once during your workflow. Keep this list-loading approach separate from a RowSource-bound ComboBox: a control bound to a worksheet range should generally be populated through its RowSource property instead of using both methods together.
- Write the button’s Click event. Return to the form designer and double-click the Save button. The editor creates the
cmdSave_Clickprocedure. Add a check so the form does not save a missing selection, then write the chosen value. This example expects a worksheet named Data and stores the selection in A2:
Private Sub cmdSave_Click()
Dim selectedDepartment As String
' A DropDownList has no selection when ListIndex is -1.
If Me.cboDepartment.ListIndex = -1 Then
MsgBox "Choose a department before saving.", vbExclamation
Exit Sub
End If
selectedDepartment = CStr(Me.cboDepartment.Value)
ThisWorkbook.Worksheets("Data").Range("A2").Value = selectedDepartment
Unload Me
End Sub
ListIndex is -1 when no list item is selected. The Value property returns the selected item here, and ThisWorkbook means the workbook containing the VBA code. If you want to keep the form open after saving, remove Unload Me. If your worksheet or destination cell differs, edit the corresponding names in the code.
- Add a way to show the form. In the VBA editor, choose Insert > Module. Add a public procedure in the standard module, replacing
UserForm1with the actual (Name) property of your form if you changed it:
Public Sub ShowDepartmentForm()
' Display the data-entry form.
UserForm1.Show
End Sub
To test it, click inside the procedure and press F5, or return to Excel and run ShowDepartmentForm from Developer > Macros. You can also assign the macro to a worksheet button. Do not put the show procedure inside the UserForm’s code module if you want it listed as a standard macro to run from Excel.
- Test both a valid choice and the empty case. Open the form, select an item, click Save, and check Data!A2. Open it again and click Save without choosing an item; the warning should appear and the form should remain open. If you allow typing in the ComboBox, test that behavior separately and decide whether values not already in the list should be accepted.
Common Mistakes
- Using the wrong control name: If the code says
cboDepartmentbut the control is still namedComboBox1, VBA will report that the method or data member was not found. Confirm the spelling in the Properties window. - Putting list-loading code in the wrong event: Code in a button’s Click event runs only after that button is clicked. Put initial choices in
UserForm_Initializeso they are present as soon as the form is shown. - Mixing RowSource and AddItem: A ComboBox that reads a worksheet range through RowSource is already bound to a list source. Trying to add items to that same bound control can cause errors or confusing results. Pick one approach for a given control.
- Assuming a typed value is a selected list item: In an editable ComboBox, a user can type text that is not in the list. A check for
ListIndex = -1will reject typed text, even if the text box is not blank. Decide whether that is the desired rule. - Saving as .xlsx or expecting Ctrl+Z to undo VBA: Use .xlsm to retain the macro. Macro actions generally cannot be undone with Excel’s Undo command, so keep a backup and test code with sample data first.
Tips
For a short, fixed list, using .AddItem is straightforward and keeps the choices visible in the form’s code. For a list that changes often, put the values in a dedicated worksheet range and configure the ComboBox’s RowSource property to that range, for example Lists!A2:A20. Keep the source list free of blank rows if you do not want blank choices. If you use a sheet name containing spaces, set the RowSource in the Properties window and verify that the range resolves correctly in your Excel version.
Choose the ComboBox Style based on the data rules. A drop-down-list style limits the choice to listed items; an editable style permits typing. Consider setting the control’s MatchRequired property when users may type but must enter a value that matches an item. Add a Label beside the control to explain what to select, and use meaningful captions on buttons. For larger lists, organize choices alphabetically or group them with a second control rather than making users scan a long, unstructured dropdown.
If you need to save more than the displayed text, a ComboBox can use multiple columns, such as a department name and an internal ID. Set properties such as ColumnCount and BoundColumn deliberately, and confirm which column its Value returns before writing data. For a simple form, storing a single selected text value is less error-prone. On Windows, use Alt+F11 to return to the VBA editor and F5 to run the active procedure; on a Mac, the editor shortcut can vary by keyboard and Excel release, so use the Tools menu if necessary.
Troubleshooting
The ComboBox is empty: Confirm that the code is in UserForm_Initialize, that the form being shown is the one containing the control, and that the control name in code matches its (Name) property. If using RowSource, check the worksheet name, range address, and whether the source cells actually contain values. If the workbook was opened with macros disabled, close it and reopen it with macros enabled only if the file is trusted.
VBA highlights a line with a compile error: Check for misspelled control and worksheet names, unmatched quotation marks, and code pasted into the wrong module. The form’s event procedure must be in the UserForm’s code module. A public macro that shows the form belongs in a standard module. In the editor, choose Debug > Compile VBAProject to locate compile-time issues before running the form.
Clicking Save gives a subscript error: The example refers to a sheet named Data. Make sure that worksheet exists in the workbook containing the code, or replace Data with its exact tab name. If it saves the wrong item, check whether the control is editable and whether the intended choice is selected. If the form closes unexpectedly, verify the location of Unload Me and any error-handling code you have added.
The macro will not run: Check that the workbook is saved as .xlsm and that macros are allowed for this trusted file under your organization’s security rules. Excel for the web does not run VBA UserForms; open the workbook in the desktop application. On Mac, controls or properties may not behave exactly as on Windows, so simplify the control setup and verify compatibility in the Mac version being used.
Frequently Asked Questions
What is the difference between a ComboBox and a ListBox?
A ComboBox displays one current value and opens a dropdown of choices; an editable ComboBox can also accept typed text. A ListBox displays several choices at once and is useful when selecting multiple items or when the whole list should remain visible.
Can I load a ComboBox from worksheet cells?
Yes. Set the control’s RowSource property to a worksheet range, such as Lists!A2:A20, or write VBA that reads values from cells and adds them to the list. For a RowSource-bound control, avoid also calling .AddItem on that same control.
How do I make users choose only from the list?
Set the ComboBox Style property to 2 - fmStyleDropDownList. You can also check ListIndex before saving and stop the procedure when it is -1, which means no item is selected.
How do I get the selected value?
Read the control’s Value property, as in Me.cboDepartment.Value. For a simple one-column list, this returns the selected text. Use ListIndex to check whether the user selected an item before using the value.
Will this UserForm work in Excel for the web?
No. Excel for the web does not run VBA macros or VBA UserForms. Open the macro-enabled workbook in the desktop Excel application. Excel for Mac supports VBA, but the available controls and behavior can differ, so test the form on the target Mac version.
Related
- Excel File Extension Corrupted? Causes and Safe Fixes
- Excel File Contains Corrupted Data? Ways to Recover It
- Extract Data From a Corrupted Excel File: Safe Methods That Work
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Find the First Row Above a Value ThresholdTo find the first row where a cell’s value is greater than a threshold, loop through the relevant…
- Excel VBA: Find a String Within a StringUse VBA’s InStr function to find one string inside another: it returns the character position of the first…
- Excel UserForm Keeps Changing Size? Causes and FixesAn Excel UserForm usually changes size because VBA or its AutoSize setting changes its dimensions, or because Windows…
- Excel VBA Exit Sub: How It Works and When to Use ItIn Excel VBA, Exit Sub immediately ends the currently running Sub procedure and returns control to the code…
- Excel VBA Select Case Examples: Syntax and Common UsesIn Excel VBA, Select Case chooses which block of code to run based on the value of an…
- How to Turn Off a TinyTask Macro and Stop PlaybackTo stop a TinyTask macro that is playing, press TinyTask’s Play/stop hotkey—Ctrl+Shift+Alt+P by default—or click the Play button.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.