An Excel UserForm ComboBox is a VBA form control that lets someone choose an item from a drop-down list, and, depending on its style, type a value too. In Excel for Microsoft 365 on Windows, add a ComboBox to a UserForm, fill it in the form’s Initialize event, and read its Value when the user submits the form.
What a UserForm ComboBox Does
A ComboBox combines a text field and a drop-down list. It is useful when a workbook needs an input screen—for example, a form for logging orders, assigning a department, or choosing a product—without asking users to type every value themselves. Because the options can come from a controlled list, it can also help prevent inconsistent entries such as “Sales,” “sales,” and “Sales team.”
A UserForm is a custom window built with VBA. Its ComboBox is different from a drop-down created with Data Validation in a worksheet cell, and it is also different from a Combo Box inserted directly on a worksheet. Those controls have different setup steps and behavior. This guide covers the ComboBox placed on a VBA UserForm.
Common reasons to use one include:
- Offering a short list of approved choices.
- Letting the user type a new value when the list is not exhaustive.
- Collecting several inputs in one window before writing them to a sheet.
- Loading choices from a worksheet so a list can be maintained without editing the form’s code.
Create the UserForm and Add the Control
In Excel for Microsoft 365 on Windows, open the Developer tab and select Visual Basic. If the tab is missing, enable it through File > Options > Customize Ribbon, then check Developer. In the Visual Basic Editor, choose Insert > UserForm. The Toolbox should appear; if it does not, select View > Toolbox. Click the ComboBox tool and draw the control on the form.
Select the control and use the Properties window to give it a clear (Name), such as cboDepartment. Set the form’s name to frmEntry if you want to use the example code below. Names matter: VBA refers to controls by their (Name), not by the caption or by the text shown in the list. Add a CommandButton, name it cmdOK, and set its Caption to something like “Save.”
The form’s layout and labels are for the user; the control names are for the code. For example, a label may display “Department,” while the ComboBox remains named cboDepartment. Keeping those roles distinct makes event code easier to read and maintain.
Fill the ComboBox from a Worksheet List
For a list that workbook users may update, store the choices on a worksheet. In this example, the sheet named Lists has a heading in A1 and department names in A2 downward. Put this code in the UserForm’s code window. The Initialize event runs when the form is loaded, so the options are assembled before the user interacts with the form.
Option Explicit
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
' Read the department choices from the Lists worksheet.
Set ws = ThisWorkbook.Worksheets("Lists")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
With Me.cboDepartment
.Clear
.Style = fmStyleDropDownList
For r = 2 To lastRow
If Len(Trim$(CStr(ws.Cells(r, 1).Value))) > 0 Then
.AddItem CStr(ws.Cells(r, 1).Value)
End If
Next r
End With
End Sub
The style fmStyleDropDownList restricts users to the available entries; they cannot type an unlisted department. If typing should be allowed, use fmStyleDropDownCombo instead. Keep the list cells free of errors and use a consistent layout. The code skips blank cells, but it does not remove duplicate choices or sort them.
For a very short fixed list, calling .AddItem once for each value is also reasonable. A worksheet-backed list is generally easier to maintain when choices change often, since a user can update the source cells without changing VBA. Another option is the control’s RowSource property, which can point to a worksheet range; it is convenient for a simple fixed range, while code that finds the last used row can adapt better as the list grows.
Read the Selection and Save It
A ComboBox’s Value property contains the current text, while ListIndex indicates the selected item’s position. If no list item is selected, ListIndex is -1. Checking it before saving is a simple way to require a choice. The following Click event belongs in the same UserForm module; it writes the selected department to the next available row in column B of a worksheet named Data, keeping row 1 for a heading.
Private Sub cmdOK_Click()
Dim ws As Worksheet
Dim nextRow As Long
' Require a selection before writing to the sheet.
If Me.cboDepartment.ListIndex = -1 Then
MsgBox "Choose a department.", vbExclamation
Exit Sub
End If
Set ws = ThisWorkbook.Worksheets("Data")
nextRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row + 1
If nextRow < 2 Then nextRow = 2
ws.Cells(nextRow, 2).Value = Me.cboDepartment.Value
Unload Me
End Sub
To open the form, add this procedure to a standard module using Insert > Module. The workbook must contain a form named frmEntry.
Public Sub ShowEntryForm()
' Display the data-entry form.
frmEntry.Show
End Sub
You can run ShowEntryForm from the Macro dialog or assign it to a worksheet button. Before running a macro, save a copy of the workbook: VBA actions cannot be reversed with Ctrl+Z. Save the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA code.
Useful Properties and Events
Several ComboBox properties control what the user sees and what the code can read. Set them in the Properties window or in VBA. The exact choice depends on whether users must select an approved value or may enter their own.
Styledetermines whether typing is allowed. UsefmStyleDropDownListfor selection only orfmStyleDropDownComboto allow typed text.ListIndexidentifies the selected row, starting at zero. A value of -1 means there is no selected list item.Valuereturns the current text or selected value. Validate it before using it as a required field.MatchRequiredcan require typed entries to match a list item when the editable style is used. Explicit validation in the submit event can still make the rule clearer.ColumnCountandColumnWidthscan display more than one column, such as a product name alongside an ID. Set them carefully because the first column is commonly the displayed value.
The Change event runs when the ComboBox’s text or selection changes, which is useful for updating another control in response. The CommandButton’s Click event is usually a better place to validate all required fields and save a complete record. Avoid putting save logic in Change: it can run repeatedly while the user is still making a choice.
Choose a Data Source That Fits the List
There is no single best way to populate every ComboBox. For a small list that will never change, hard-coded AddItem calls are straightforward. If users maintain the choices, a worksheet range is more transparent. A control’s RowSource property can connect it directly to a range, but the range reference must be valid and changes to the sheet can affect what appears. Code using AddItem offers more control—for example, it can skip blanks or apply other checks—but the form must be refreshed when the source changes.
For a growing list, consider using a named range or a worksheet table as the maintained source, then adjust the loading code to find the table’s data rows. A named range can make a reference easier to understand; a table expands as rows are added. If a form is already open when its source list changes, it will not necessarily refresh by itself. Reload the control deliberately, or close and reopen the form.
Common Problems and How to Fix Them
- The list is empty: Confirm the worksheet name, source column, and first data row. Check that the code is in the correct form’s
UserForm_Initializeevent and that the form is being loaded rather than merely viewed in the editor. - “Subscript out of range” appears: A referenced worksheet or workbook object may not exist under the exact name in the code. Check spelling, spaces, and whether the list is in the workbook referenced by
ThisWorkbook. - The form rejects typed text: That is expected with
fmStyleDropDownList. ChoosefmStyleDropDownComboif free typing is intended, and add validation if only certain values are acceptable. - The selected value is blank or missing: Make sure the user made a choice and check
ListIndexbefore saving. Also confirm that the submit button’s Click event is attached to the button’s actual (Name). - New list entries do not appear: A fixed range may not include the added rows, or the form may have loaded before the list was updated. Expand the source or reload the ComboBox after changes.
For debugging, use the Visual Basic Editor’s Debug > Compile VBAProject command to catch many syntax and compile-time errors. A compile check will not detect every runtime issue, such as a missing sheet, so verify worksheet names and test the form with a copy of the workbook and representative data.
When a UserForm ComboBox Is Not the Best Choice
A UserForm is useful when data entry needs a dedicated window, several controls, or VBA-driven behavior. It may be unnecessary for a single choice in a worksheet cell. For that situation, Excel’s Data > Data Validation list can provide a drop-down without a custom form or VBA. A worksheet Combo Box control is another possibility when the control must sit directly on the sheet, though it has its own setup and linking behavior.
Use a ListBox instead when users need to see several choices at once or, with the appropriate settings, select multiple items. A ComboBox is designed around one current value, not a multi-select result. Also consider the workbook’s audience: a VBA UserForm is not the same as a form that works in Excel for the web, where VBA macros do not run.
Windows, Mac, and Macro Considerations
The steps and code here target desktop Excel for Microsoft 365 on Windows. The Visual Basic Editor can generally be opened with Alt+F11 on Windows. On Mac, Option+F11 may work, sometimes with Fn as well depending on keyboard settings, but the VBA environment and control support differ. Test a UserForm on the exact Mac version and devices that will use the workbook; do not assume Windows form behavior will transfer unchanged. ActiveX worksheet controls are not supported on Mac.
Excel for the web does not run VBA macros, so a UserForm that depends on this code is not an option for web-only users. Macros may also be blocked by security settings or organizational policy. Share macro-enabled workbooks only with appropriate users, and follow the organization’s rules for trusted files and signed code.
Frequently Asked Questions
How do I show a UserForm ComboBox in Excel?
Open the form with its .Show method, such as frmEntry.Show in a standard-module macro. The ComboBox is part of the form and appears when the form opens.
Can users type a value into a UserForm ComboBox?
Yes, if its Style is fmStyleDropDownCombo. With fmStyleDropDownList, users must choose an item from the list.
How can I make a ComboBox selection required?
In the submit button’s Click event, check whether ListIndex is -1. If it is, show a message and exit the event without saving.
Can a UserForm ComboBox allow multiple selections?
No. A ComboBox represents one current selection or value. Use a ListBox configured for multiple selection if users need to choose several items.
Will a VBA UserForm work in Excel for the web?
No. Excel for the web does not run VBA macros, so a VBA UserForm cannot provide its data-entry behavior there. Consider worksheet Data Validation or another supported form approach for web-based workflows.
Related
- Data Validation From a Table in Excel: Setup and Fixes
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
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 Last Row with Data in a RangeTo find the last row containing data within a particular Excel range, use the range’s Find method with…
- Excel VBA InputBox Password: Masking and Secure OptionsExcel’s built-in VBA InputBox cannot hide a password as the user types; it displays the entered characters. To…
- Macro Recorder for Windows: Options and How to ChooseA macro recorder for Windows captures repeated actions and replays them, but Windows does not provide one universal…
- VBA Sort Dictionary Keys: Methods and ExamplesTo sort VBA dictionary keys, copy them into the array returned by Dictionary.Keys, sort that array, and loop…
- VBA SortFields.Add2: What It Does and How to Use ItSortFields.Add2 is an Excel VBA method that adds a sort key to a sort operation. It works much…
- Excel VBA Delete Row: Methods, Examples, and Best PracticesTo delete a worksheet row with Excel VBA, reference the worksheet and call its Rows collection’s Delete method—for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.