Excel VBA ComboBox: Load a UserForm List From Range

To fill an Excel VBA UserForm ComboBox from cells, set its RowSource property to a worksheet range, or add cell values with VBA’s AddItem method. For a simple, continuously connected list, use RowSource; use AddItem when you need to filter, clean, or otherwise control which values appear.

How a UserForm ComboBox gets its list

A ComboBox is a control that lets a person choose an item from a drop-down list and, depending on its settings, type a value as well. In the Visual Basic Editor, a ComboBox placed on a UserForm has a RowSource property that can point to cells, and its AddItem method can build a list one item at a time. Both approaches populate the control; they differ in how much control you have over the source values.

For a small lookup list that already sits in one contiguous block, RowSource is usually the shortest route. The ComboBox refers to the cells directly, so changes to the source range can be reflected when the form is used. For a list that needs blank values removed, conditional filtering, or values assembled from more than one place, read the cells in VBA and add only the items you want.

The examples below are for desktop Excel for Microsoft 365 on Windows. The Excel desktop application is required to run VBA; Excel for the web can open a macro-enabled workbook but cannot run its VBA code or display a VBA UserForm. Excel for Mac has VBA support, but control and compatibility details can differ, so check the specific workbook and controls on the Mac version you need to support.

Prepare the worksheet range

Put the list on a worksheet and decide whether its first row is a heading. A common layout is a worksheet named Lookup, a heading in A1, and the selectable items in A2:A20. The heading should not be included in the ComboBox’s source range unless you want users to select it. Keeping source data in one column also makes the initial setup straightforward.

In the Visual Basic Editor, open the UserForm, select the ComboBox, and set its (Name) property to a useful name such as ComboBox1 or cmbDepartment. The name used in VBA must match the control’s actual name. Double-click the form’s background to open its code module, then use its UserForm_Initialize event to load the list when the form opens.

Save a copy of the workbook before running a macro. VBA actions cannot be reversed with Ctrl+Z, even when a particular macro only appears to load a list. Save the file in a macro-enabled format such as .xlsm if it needs to retain VBA code, and only enable macros in files you trust.

Load a fixed range with RowSource

For a known range, assign its worksheet address to RowSource. This example assumes the worksheet is named Lookup, the header is in A1, and the list occupies A2:A20. Paste the event procedure into the UserForm’s code module, not a standard module.

Private Sub UserForm_Initialize()
    ' Connect the ComboBox to the source cells.
    Me.ComboBox1.RowSource = "'Lookup'!A2:A20"
End Sub

The single quotes around the worksheet name are valid even when the name has no spaces, and they also make the reference suitable for names that do contain spaces. If the sheet is called Department List, for example, the quoted name is essential. If your control is named cmbDepartment, replace ComboBox1 with cmbDepartment.

To set the same source without code, select the ComboBox in the UserForm designer, open the Properties window, and enter the range reference in RowSource, such as Lookup!A2:A20. This is convenient for a fixed list. The event procedure is more useful when the range changes or when you want to make the setup visible and maintainable in code.

Find the last row for a changing list

If the list grows over time, a fixed endpoint such as A20 can omit new entries. Find the last occupied cell in the source column when the form initializes, then build the range address. This example assumes row 1 is a header and column A contains the list on the worksheet named Lookup.

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

    ' Use the sheet in the workbook that contains this VBA project.
    Set ws = ThisWorkbook.Worksheets("Lookup")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    With Me.ComboBox1
        .RowSource = vbNullString
        If lastRow >= 2 Then
            .RowSource = "'" & ws.Name & "'!A2:A" & lastRow
        End If
    End With
End Sub

End(xlUp) starts at the bottom of column A and moves upward to the last non-empty cell. The check for row 2 avoids creating a data range when the sheet contains only its heading. If there are gaps between the first and last items, the referenced range still includes those blank cells; use the AddItem approach below if you want to omit them.

This method assumes the list is in column A and that the header is in row 1. If the list is in another column or begins on another row, change both the column used to find lastRow and the address supplied to RowSource. ThisWorkbook means the workbook containing the VBA code, which helps avoid accidentally reading a different workbook that happens to be active.

Use AddItem to filter or clean values

When the source range contains blanks you do not want to show, or you want to decide which rows qualify, add values in a loop instead. This example includes nonblank entries from A2 down to the last used row. It assumes the cells contain ordinary text or numbers rather than Excel error values.

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range

    ' Read the list from the workbook containing this form.
    Set ws = ThisWorkbook.Worksheets("Lookup")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    With Me.ComboBox1
        .RowSource = vbNullString
        .Clear
        For Each cell In ws.Range("A2:A" & lastRow).Cells
            If Len(Trim$(CStr(cell.Value2))) > 0 Then
                .AddItem CStr(cell.Value2)
            End If
        Next cell
    End With
End Sub

The loop uses Value2 to read each cell’s value and Trim$ to treat cells containing only spaces as blank. If the sheet has no data below the heading, the constructed address is A2:A1; avoid that case by wrapping the loop in If lastRow >= 2 Then. For example, put the For Each loop inside that condition so it runs only when at least one data row exists.

Unlike a direct range reference, items added this way are copied into the control’s list when the event runs. Later edits to the worksheet do not automatically rebuild that list; reopen the form or run a refresh procedure to reload it. You can also add an If condition in the loop to include only rows meeting a rule, such as entries marked active in a neighboring column.

Choose between RowSource and AddItem

Neither method is always better. Match the method to the shape of the list and the behavior you want. A practical comparison is:

Choose RowSource for a simple, contiguous range that should remain tied to the worksheet cells. It takes little code, but blank rows in the range remain part of the source.
Choose AddItem when you need to skip blanks, apply conditions, combine values, or build a list that is not a single contiguous range. It needs a loop and must be run again to refresh the displayed items.
Use a named range when a fixed or managed range deserves a descriptive name. A name such as DepartmentChoices can make the source easier to recognize, but the name must resolve correctly for the workbook and form.

Excel tables can also make a growing source list easier to manage because their data body expands as rows are added. A table’s DataBodyRange represents its data rows, excluding the header. In VBA, you can read that range and loop through it with AddItem; check that the table has data first, since an empty table has no data body to loop through. This is often more robust than assuming that a worksheet column’s last used row always belongs to the list.

Show multiple columns in the drop-down

A ComboBox can display more than one source column, which is useful for choices such as an ID alongside a readable name. Set ColumnCount to the number of columns in the source, then point RowSource at the matching rectangular range. For example, if IDs are in A2:A20 and names are in B2:B20, use the two-column range A2:B20 and set ColumnCount to 2.

Set ColumnWidths to control how wide each displayed column is. For example, a width string such as 0 pt;120 pt hides the first column and displays the second at 120 points. Set BoundColumn deliberately: it determines which column supplies the ComboBox’s value. If the ID in the hidden first column is the value you need after selection, keep that column bound while displaying the name column. Test the selected value in the form because the visible text and returned value can differ.

For a two-column list built with AddItem, add the first column’s value with AddItem, then fill another column on the newly added row using the ComboBox’s List property. This is more involved than loading a rectangular range with RowSource, so prefer the range method when the source is already arranged in columns and does not need filtering.

Common problems and practical checks

The list is empty: Check that the sheet name, range, and control name are correct. Confirm the data rows exist where the code expects them and that the initialization procedure is in the UserForm module.
The header appears as an option: Start the source at row 2 rather than row 1, or adjust the start row to match your layout.
New rows do not appear: A fixed address does not expand itself. Use a calculated last row, a managed named range, or reload the list after the source changes.
The code errors when changing the list: A ComboBox whose RowSource is set is linked to that range. Clear RowSource before using .Clear or adding items manually.
The wrong workbook is read: Use ThisWorkbook when the source belongs to the workbook containing the macro. ActiveWorkbook can refer to a different workbook if the user switches files.
The selected result is unexpected: Review BoundColumn, especially when the list has hidden or multiple columns. The displayed name is not necessarily the value returned by the control.

For troubleshooting, temporarily inspect the calculated lastRow and the final range address in the VBA editor, or set a breakpoint in UserForm_Initialize and step through the code. Also check for error values in source cells if a loop converts cell contents with CStr; an error value should be checked before conversion if it may occur.

Frequently Asked Questions

How do I make a UserForm ComboBox list update when cells change?
Use a dynamic source range or reload the list after the cells are updated. A RowSource range points to worksheet cells, while values added with AddItem are copied into the control and need to be added again after changes.

Can a ComboBox use a range on another worksheet?
Yes. Include the worksheet name in the RowSource reference, such as 'Lookup'!A2:A20. If the sheet name contains spaces, keep it enclosed in single quotes.

Can I leave out blank cells?
Yes. Loop through the source with AddItem and add only cells that are not blank. A direct RowSource to a range containing gaps does not remove those gaps for you.

Why does ComboBox1.Clear give an error?
A ComboBox linked through RowSource cannot be treated like a manually built list until that link is removed. Set RowSource to an empty string first, then clear or add items.

Does this work in Excel for the web?
No. Excel for the web does not run VBA macros or show VBA UserForms. Use desktop Excel to run this kind of form; if web access is essential, consider a worksheet-based selection interface instead.

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.