Home / Tutorials

Intermediate 10 min read Updated 21/09/2026

Adding Information to ComboBox Controls in Excel UserForms

Learn how to populate and manage ComboBox controls in Excel UserForms using VBA, including different methods to add items, handle events, and implement dynamic data loading.

In this tutorial:

Creating a ComboBox in UserForm
Multiple methods to add items
Loading data from worksheet
Handling dynamic updates
Best practices and examples

You'll need:

Excel (any version)
VBA Editor access
Basic VBA knowledge

Adding Information to ComboBox in Excel UserForm

Creating UserForm and ComboBox

Step-by-Step Setup:

1

Open VBA Editor

Press Alt + F11 or right-click sheet tab → View Code
2

Insert UserForm

Insert → UserForm
3

Add ComboBox

Toolbox → ComboBox → Draw on UserForm

Method 1: Adding Items Directly

Using AddItem Method


                        Private Sub UserForm_Initialize()

                            With ComboBox1

                                .Clear

                                .AddItem "Option 1"

                                .AddItem "Option 2"

                                .AddItem "Option 3"

                            End With

                        End Sub

Using List Property


                        Private Sub UserForm_Initialize()

                            ComboBox1.List = Array("Option 1", "Option 2", "Option 3")

                        End Sub

Method 2: Loading Data from Worksheet

Using Range Method


                        Private Sub UserForm_Initialize()

                            Dim ws As Worksheet

                            Set ws = ThisWorkbook.Sheets("Sheet1")

                            With ComboBox1

                                .Clear

                                .List = ws.Range("A2:A10").Value

                            End With

                        End Sub

Using Dynamic Range

A changing list needs separate handling for no values, one value and multiple values. Use the complete worksheet refresh procedure below instead of extending the fixed A2:A10 example.

Method 3: Advanced Features

Multiple Columns in ComboBox


                        Private Sub UserForm_Initialize()

                            Dim ws As Worksheet

                            Set ws = ThisWorkbook.Sheets("Sheet1")

                            With ComboBox1

                                .ColumnCount = 2

                                .ColumnWidths = "100;100"

                                .List = ws.Range("A2:B10").Value

                            End With

                        End Sub

Auto-Complete Feature


                        Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

                            Dim i As Long

                            Dim searchText As String

                            searchText = ComboBox1.Text

                            For i = 0 To ComboBox1.ListCount - 1

                                If UCase(Left(ComboBox1.List(i), Len(searchText))) = UCase(searchText) Then

                                    ComboBox1.ListIndex = i

                                    Exit Sub

                                End If

                            Next i

                        End Sub

Best Practices

Do's:

Clear ComboBox before adding items
Use error handling for data loading
Set appropriate default values

Don'ts:

Forget to validate user input
Hard-code ranges without checks
Ignore performance with large datasets

Refresh a ComboBox from a worksheet

Put this procedure in the UserForm code module. It assumes a worksheet named Data, labels in A1 and values from A2 downward. Call RefreshComboBox from UserForm_Initialize and after the source list changes. A single cell is added with AddItem so the range-to-array assignment remains two-dimensional.

Private Sub UserForm_Initialize()
    RefreshComboBox
End Sub

Private Sub RefreshComboBox()
    Dim ws As Worksheet
    Dim lastRow As Long
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ComboBox1.Clear
    If lastRow < 2 Then Exit Sub
    If lastRow = 2 Then
        ComboBox1.AddItem CStr(ws.Range("A2").Value)
    Else
        ComboBox1.List = ws.Range("A2:A" & lastRow).Value
    End If
End Sub

A missing Data sheet raises an error; create it or change the name in the procedure. Reject blank/error cells or clean them in the worksheet before loading. The earlier fixed-range examples remain useful for a small known list; do not paste multiple UserForm_Initialize procedures into the same form.

Final Tips for Success

Maintenance

Regularly update data sources
Document any custom functions

Testing

Test with various data sizes
Verify error handling

User Experience

Add helpful tooltips
Provide feedback messages