Excel VBA UserForm Dynamic List: Populate and Update

To create a dynamic list in an Excel VBA UserForm, clear a ListBox or ComboBox and refill it from the current worksheet data when the form opens. For a list that must also respond to changes made while the form is open, put the loading code in a reusable procedure and call it again after the source data changes.

What “dynamic list” means in a UserForm

A UserForm list is dynamic when its displayed choices come from data that can change, rather than being permanently typed into the control’s properties or VBA code. For example, a product-selection ComboBox might read product names from a worksheet, so adding a product to that worksheet makes it available in the form after the list is refreshed.

There are two separate moments to consider: loading the form and changing the underlying data. The UserForm_Initialize event is a convenient place to load choices when a form instance is first created. It does not run every time a form that is already loaded is hidden and shown again. If users can edit the source list while the form remains open, call the loading procedure again after the edit, or provide a refresh button.

“Dynamic” does not automatically mean live updating. VBA needs to know when to read the source again. A worksheet formula or table can update its own values, but a UserForm control does not necessarily refresh its displayed items just because the cells changed.

Choose a ComboBox or ListBox

A ComboBox is usually the better choice when the user should select one item without taking up much space. It can show a drop-down list and, depending on its Style property, may also let the user type a value. Set Style to fmStyleDropDownList when users must choose only an existing item; leave the editable style when entering a new value is allowed.

A ListBox keeps several choices visible at once and is useful when users need to scan options or select multiple items. Its MultiSelect property controls whether the user can select one or several entries. A multi-select ListBox needs different code when you read the selection: inspect each item’s Selected property rather than relying on the control’s single Value.

Both controls can be populated with AddItem. In the VBA editor, open the UserForm, select the control, and set its Name property—for example, cboProduct or lstProducts. Names used in the code must match the actual control names. For a simple one-column list, AddItem is straightforward; a multi-column list may need additional column setup and code to fill the other columns.

Set up the worksheet and form

For the example below, create a worksheet named Products. Put a heading in A1 and product names in A2 downward, with one name per row. The sample code finds the last non-empty cell in column A and skips blank cells within the list. It accepts text and numbers that Excel can convert to text, and it skips cells containing errors.

In desktop Excel for Microsoft 365 on Windows, enable the Developer tab from File > Options > Customize Ribbon by checking Developer.
Open the Visual Basic Editor with Alt+F11, then choose Insert > UserForm. Add a ComboBox from the Toolbox and name it cboProduct.
Double-click the form’s background to open its code module. Put the following procedures in that module, changing the worksheet, column, or starting row if your layout differs.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). A workbook saved as .xlsx cannot retain VBA code.

On a Mac, Excel also supports VBA, but available controls and behavior can differ from Windows. Open the Visual Basic Editor from the Developer tab or the Tools menu; depending on the keyboard, the shortcut may be Option+F11 or Fn+Option+F11. Excel for the web cannot run VBA macros or display VBA UserForms, so use the desktop application for this solution.

Populate the list from worksheet data

This example reads the worksheet each time the form is initialized. RefreshProducts is also exposed so other code in the project can request an update. The code assumes the Products sheet exists and that the list begins in A2.

Option Explicit

Private Sub UserForm_Initialize()
    LoadProducts
End Sub

Public Sub RefreshProducts()
    LoadProducts
End Sub

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

    Set ws = ThisWorkbook.Worksheets("Products")
    Me.cboProduct.Clear

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    For Each cell In ws.Range("A2:A" & lastRow)
        If Not IsError(cell.Value2) Then
            If Len(Trim$(CStr(cell.Value2))) > 0 Then
                Me.cboProduct.AddItem CStr(cell.Value2)
            End If
        End If
    Next cell
End Sub

The sequence matters: Clear removes the old choices, the last-row calculation determines how far down column A to look, and AddItem inserts each nonblank value. Clearing first prevents duplicates from accumulating when the procedure is run more than once. The loop preserves the order of the worksheet rows; it does not sort the list or remove duplicate names.

This code uses ThisWorkbook, which means the workbook containing the VBA project. That is usually safer than ActiveWorkbook: another workbook may become active while the form is open. If the list is on a different workbook, change the worksheet reference deliberately rather than relying on whichever workbook happens to be active.

Refresh the list after data changes

After the form opens, call RefreshProducts again whenever code changes the source list. For example, if a button adds a new product to the worksheet, run the refresh procedure after writing the new value. If the form’s code performs the edit itself, it can call LoadProducts directly. If a separate module needs to refresh the form, call the public method on the form instance being used; projects that create multiple form instances should keep a reference to the correct instance.

A simple refresh button can use a Click event to reload the choices. Alternatively, refresh immediately after the user saves a new entry. The latter is less confusing because the list updates as part of the action that changed the data. Avoid refreshing on every keystroke in a large source list: repeated worksheet scans can make the interface feel slow. For a modest list, scanning a single column is generally uncomplicated; for a much larger list, consider reading the range into an array and filtering it in memory.

If the list is populated when the form first opens but looks stale after the user returns to it, check whether the form was hidden rather than unloaded. Initialize runs when the form is loaded, not on every activation. You can refresh in the form’s Activate event if that behavior is appropriate, but remember that Activate may run repeatedly as focus returns to the form. In many projects, an explicit refresh after a known data change is easier to reason about.

Use an Excel table or a dependent list

An Excel Table is a useful way to organize a changing source list. It expands when users add rows directly beneath it, which makes the source data easier to manage and gives the workbook a clear heading and consistent structure. The sample code still calculates the last used row in column A, so it can read newly added entries without a fixed end-row such as A2:A20. Keep the list in its own column if other data could make the last-row calculation misleading.

For a dependent list—for example, choosing a department first and then showing only its employees—use the first control’s Change event to rebuild the second control. The handler should clear the dependent control, read the selected department, and add only matching employee names. Check that the parent selection is not blank before filtering. This is a different task from merely loading one list: the filtering rule and the relationship between the source columns must be defined as well.

When source data is maintained by formulas, confirm that the cells contain the values you expect before converting them to strings. If formula results can be blank, the sample’s Trim check omits them. If you need to preserve leading or trailing spaces as meaningful characters, remove the trimming step and define how blank entries should be handled.

Common problems and how to fix them

The list is empty: Verify the worksheet name, control name, and starting row. Check that the values are in the column the code scans and that the workbook is macro-enabled with macros permitted by its security settings.
The list has repeated entries: The code adds every nonblank cell, including duplicates. Remove duplicates from the source, or add a deliberate duplicate-checking step if unique choices are required.
Old choices remain or entries multiply: Make sure the procedure calls Clear before the loop. If you have multiple controls, confirm that the code clears and fills the intended one.
New worksheet entries do not appear: Run the refresh procedure after the edit. Initialize is not a continuous watcher, and changes made manually on a worksheet do not automatically call the form’s refresh method.
A type mismatch or missing sheet error appears: Check the sheet spelling and workbook reference. The sample skips Excel error values, but unusual data or changes to the source layout may require additional validation.
Items appear in the wrong order: AddItem follows the order read from the cells. Sort the source worksheet or sort the values before adding them if alphabetical order is required.

Also distinguish between a control’s list contents and its selected value. Reloading a ComboBox may clear or change the current selection. If the form must preserve a selection, store the selected text before clearing, refill the control, and then look for that text among the new items before restoring it. If the old choice was deleted from the source, decide whether the form should leave the selection blank or show a validation message.

RowSource, AddItem, and alternatives

RowSource is another way to connect a ListBox or ComboBox to a worksheet range. It can be convenient when a fixed range is sufficient and the displayed list should mirror worksheet cells. However, it is less flexible when code must skip blanks, filter entries, or build a list from several places. A fixed address can also omit newly added rows unless the range is updated or uses an appropriate expanding source. Do not set RowSource and also populate the same control with AddItem; choose one approach for a given list.

AddItem gives VBA direct control over which values enter the control and when they are refreshed. It is a good fit for filtering, validation, and lists whose size changes. For more elaborate interfaces, other options include a worksheet-based data-entry form, a table with filters, or a non-VBA solution such as a Microsoft 365 form or an Office Script workflow. Those alternatives are not interchangeable with a VBA UserForm, and Office Scripts do not run VBA UserForms.

Macro safety and workbook compatibility

Only enable macros in workbooks you trust. VBA can change workbook data and perform actions beyond what a displayed list suggests, so review code from unfamiliar files and follow your organization’s macro policy. Before running or modifying a macro, save a separate copy of the workbook. Macro actions generally cannot be reversed with Ctrl+Z, so a backup is important even for a small UserForm change.

Excel for Microsoft 365 on Windows is the reference environment for the steps here. Excel for Mac supports VBA, but UserForm controls and editor behavior can vary, so verify the form on the Mac version that will use it. Excel for the web does not execute VBA. If recipients need to use the workbook in a browser, provide a non-VBA route for the task rather than assuming the UserForm will work there.

Frequently Asked Questions

How do I make a UserForm list update automatically?

Call the list-loading procedure after the source data changes. Initialize loads it when the form is first created, but worksheet edits do not automatically refresh a control. An explicit refresh button or a call after saving a new entry is usually the clearest approach.

Can a ComboBox read its choices directly from a worksheet?

Yes. You can use RowSource for a worksheet range or use VBA to loop through cells and add values with AddItem. Use AddItem when you need to skip blanks, filter records, or control precisely when the list is rebuilt.

Why does UserForm_Initialize not run every time I show the form?

Initialize runs when a form instance is loaded. Hiding a loaded form and showing that same instance again does not create a new instance, so the event may not run again. Refresh explicitly or use an appropriate activation event if refreshing on return is intended.

How do I remove blank rows from a dynamic list?

Test each cell before adding it. The example converts the value to text, trims surrounding spaces, and adds it only when the result is not empty. It also skips cells with Excel error values.

Will a VBA UserForm work in Excel for the web?

No. Excel for the web does not run VBA macros or show VBA UserForms. Use desktop Excel for this form, or design a separate browser-compatible process for users who work only in Excel for the web.

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.