Excel UserForm ComboBox: Load a List with VBA

To load an Excel UserForm ComboBox from worksheet values, add a ComboBox to the form, then fill it in the form’s UserForm_Initialize event with VBA. For example, loop through the values in a worksheet column and use AddItem; the list is then ready when the form opens.

What the UserForm Initialize event does

A UserForm is a custom dialog you build in Excel’s Visual Basic for Applications (VBA) editor. Its ComboBox control lets someone choose an item from a drop-down list and, depending on its settings, type a value of their own. The list can come from a worksheet range, values entered directly in code, or data assembled by the program.

UserForm_Initialize runs when the form is loaded, before it is displayed for the first time. It is a convenient place to prepare the controls, including clearing and filling a ComboBox. If you want the list refreshed every time a user opens the form, unload the form when closing it; hiding an already loaded form does not rerun Initialize when it is shown again.

The instructions below target Excel for Microsoft 365 on Windows. VBA and desktop UserForms are not available in Excel for the web, so a workbook opened there cannot run this form. Excel for Mac has VBA support, but the editor, keyboard shortcuts, and some control behavior differ; test a form on the Mac versions your users rely on.

Set up the form and ComboBox

Save a working copy of the workbook as an Excel Macro-Enabled Workbook (.xlsm).
In Windows Excel, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > UserForm if you need to create a form.
Show the Toolbox if it is not visible, then add a ComboBox to the form. Select it and change its (Name) property in the Properties window to cboProduct.
In the Project Explorer, double-click the form, choose the form object and its Initialize event in the code window’s drop-downs, and place the loading code in that event.

In the example, the worksheet is named Products, row 1 contains a heading, and product names are in column A from row 2 down. Change the sheet name, column, and control name to match the workbook.

Load a worksheet column with AddItem

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    ' Read product names from the Products worksheet.
    Set ws = ThisWorkbook.Worksheets("Products")
    Me.cboProduct.Clear
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Skip the heading and ignore blank cells.
    For r = 2 To lastRow
        If Len(Trim$(CStr(ws.Cells(r, "A").Value))) > 0 Then
            Me.cboProduct.AddItem CStr(ws.Cells(r, "A").Value)
        End If
    Next r
End Sub

The code finds the last non-empty cell in column A, starting at the bottom of the worksheet and moving upward. It then adds each nonblank value to the ComboBox in worksheet order. If the column contains only a heading or is otherwise empty below row 1, the loop has no entries to add. The call to Clear prevents an existing list from being retained if the event is run again.

ThisWorkbook means the workbook containing this VBA code. That is usually safer than ActiveWorkbook, which refers to whichever workbook currently has focus. The worksheet name must match exactly. If the source values are on another sheet, update the name and keep the correct workbook reference.

Choose between AddItem and RowSource

AddItem copies values into the control one at a time. It works well when the list needs blanks removed, values transformed, or entries assembled from different places. Once populated this way, the displayed list is a copy: changing a source cell does not automatically update the ComboBox. Refill it when needed.

The alternative is the ComboBox’s RowSource property, which links the control to a worksheet range. For a short, fixed list, assign a range such as Products!A2:A20 to the property in the Properties window. A range reference avoids writing a loop, but it must be updated if the list grows beyond its end, and blank cells in the range may appear as blank entries. Worksheet names containing spaces need appropriate quoting in the reference.

Do not set both RowSource and populate the same ComboBox with AddItem. A control bound to a row source is managed through that range, and mixing the approaches can cause errors or confusing behavior. Choose one method based on whether you want a simple linked range or programmatic control over the entries.

Keep the list current as data changes

The loop example already adapts to additions in column A because it calculates the last used row each time the form initializes. It does not detect a list stored in another column, and a stray value far below the real list can make the loop scan many rows. Keep the source column tidy, or determine the last row using a column that is reliably filled for every record.

For a more maintainable source, format the data as an Excel Table using Insert > Table. Tables expand when new records are added. VBA can still loop through the table’s data rows, but the code must handle a table with no data rows and refer to the table and column by their actual names. For a modest, single-column list, the last-row method is often simpler; for structured records that change frequently, a table gives the source data clearer boundaries.

If the form stays loaded but hidden while worksheet values change, Initialize will not run again just because the form becomes visible. Put the same loading procedure in a separate routine and call it both when the form initializes and when you deliberately refresh it. This avoids relying on a form lifecycle event that has already happened.

Load more than one column

A ComboBox can display multiple columns, such as a product name alongside a product code. Set its ColumnCount property to the number of columns and adjust ColumnWidths so both are visible. For example, a two-column list might show a wide name column and a narrower code column. A zero-width column can be hidden, which is useful when users should see a name but the form needs to retain an identifier.

For a small two-column list, add the first field with AddItem, then assign the second field to the newly added row’s second column using the ComboBox’s List property. The row index for the latest item is ListCount - 1; the first column index is 0 and the second is 1. Set ColumnCount to 2 before adding entries. When reading a choice later, check the BoundColumn setting: it controls which column supplies the control’s Value. If you need both displayed fields, read them from the selected row rather than assuming Value contains the visible first column.

Common problems and fixes

The list is empty: Verify the ComboBox name, worksheet spelling, data column, and starting row. Confirm that the source values are on the sheet in ThisWorkbook, not a different open workbook.
The event does not run: The procedure must be named UserForm_Initialize and placed in that form’s code module, not in a standard module or a worksheet module.
Items appear twice: Clear the control before filling it, as in the example, and check that the loading code is not also being called from another event.
Blank entries show up: The sample skips cells that are empty or contain formulas returning an empty string. If whitespace should count as meaningful, remove the Trim$ check.
Values are missing at the bottom: Check whether the list extends past the column used to find the last row, or whether the supposed last row contains a blank in that column.
A type mismatch occurs: A source cell containing an Excel error value cannot be converted to text with CStr. Clean the source data or explicitly test for error values before converting them.

For a quick check, set a breakpoint on the line that calls AddItem and step through the procedure in the VBA editor. You can inspect lastRow and the cell value at each pass. Also check the ComboBox’s MatchRequired and Style properties if users report that they cannot type a value; those settings affect whether free-form typing is allowed.

Show the form and use the chosen value

Loading the list prepares the control; a separate macro or workbook event must show the form. A button assigned to a macro can display it with a statement such as UserForm1.Show, using the real form name. When the user confirms a choice, a command button’s click event can read Me.cboProduct.Value and place it in a worksheet cell or pass it to another procedure. Decide what should happen if the user makes no selection, since an unselected ComboBox may return an empty value.

Before running unfamiliar VBA, save a separate copy of the workbook. Macros can change data and cannot be undone with Ctrl+Z. Run macros only from workbooks and sources you trust; do not weaken Excel’s macro security settings just to make a form run.

Frequently Asked Questions

How do I load a ComboBox when a UserForm opens?
Put the list-building code in that UserForm’s UserForm_Initialize event. Clear the control, then use AddItem for each entry or set a suitable RowSource.

Can I load a ComboBox directly from a worksheet range?
Yes. Set its RowSource property to a worksheet range, such as Products!A2:A20, or loop through that range with VBA. A fixed range is quick to set up; a loop gives you more control over blanks and processing.

Why does my UserForm ComboBox show duplicate items?
The list is probably being added without clearing the control, or the loading routine runs more than once. Call Clear before adding items and check which events call the routine.

How do I refresh the list after changing the worksheet?
Run a dedicated refresh routine that clears and reloads the ComboBox. Initialize runs when a form is first loaded, not every time an already loaded but hidden form is shown again.

Can an Excel UserForm ComboBox run in Excel for the web?
No. Excel for the web does not run VBA UserForms or their macros. Use desktop Excel for the form, or consider a worksheet-based selection interface if users need to work in a browser.

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.