VBA & Macro Problems

Populate an Excel ActiveX ComboBox from a Table with VBA

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Populate an ActiveX ComboBox from the first column of an Excel table, add user-entered values, and validate ComboBox entries against the list.

Populate an Excel ActiveX ComboBox from a Table with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses an Excel table as the source of allowed values for an ActiveX ComboBox.
Observed behavior
The ComboBox should display table values, accept newly added entries, and reject typed values that are not in the list.
Before you start

Use desktop Excel on Windows, where worksheet ActiveX controls are supported, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Enable the Developer tab and run macros only if you trust the workbook.

Solution 1Recommended

Load and validate the ComboBox with worksheet VBA

Use a separate text box to add values, while restricting the ComboBox to entries already in the table.

This example uses a table named Table1 and worksheet ActiveX controls named ComboBox1, TextBox1, and CommandButton1. It loads nonblank values from the table's first column and compares entries without regard to letter case.

The text box is separate from the ComboBox so the ComboBox validation does not block a new value before the Add button can process it.

1
Create the table and controls

Select your data and choose Insert > Table. Under Table Design, name the table Table1. On the Developer tab, choose Insert and add an ActiveX ComboBox, TextBox, and CommandButton to the same worksheet.

2
Set control properties

Choose Developer > Design Mode, then right-click each control and choose Properties. Set their (Name) properties to ComboBox1, TextBox1, and CommandButton1; set the button's Caption to Add. Leave the ComboBox editable so users can type into it.

3
Add the worksheet code

Right-click the worksheet tab, choose View Code, and paste the following into that sheet's code module: Option Explicit Private Sub Worksheet_Activate() LoadComboList End Sub Private Sub LoadComboList() Dim tbl As ListObject Dim cell As Range Set tbl = Me.ListObjects("Table1") Me.ComboBox1.Clear If Not tbl.DataBodyRange Is Nothing Then For Each cell In tbl.ListColumns(1).DataBodyRange.Cells If Len(Trim$(CStr(cell.Value))) > 0 Then Me.ComboBox1.AddItem CStr(cell.Value) End If Next cell End If End Sub Private Sub CommandButton1_Click() Dim tbl As ListObject Dim cell As Range Dim newValue As String Dim newRow As ListRow newValue = Trim$(CStr(Me.TextBox1.Value)) If Len(newValue) = 0 Then MsgBox "Enter a value to add." Exit Sub End If Set tbl = Me.ListObjects("Table1") If Not tbl.DataBodyRange Is Nothing Then For Each cell In tbl.ListColumns(1).DataBodyRange.Cells If StrComp(Trim$(CStr(cell.Value)), newValue, vbTextCompare) = 0 Then MsgBox "That value is already in the list." Exit Sub End If Next cell End If Set newRow = tbl.ListRows.Add newRow.Range.Cells(1, 1).Value = newValue LoadComboList Me.ComboBox1.Value = newValue Me.TextBox1.Value = "" End Sub Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Dim enteredValue As String Dim i As Long If IsNull(Me.ComboBox1.Value) Then Exit Sub enteredValue = Trim$(CStr(Me.ComboBox1.Value)) If Len(enteredValue) = 0 Then Exit Sub For i = 0 To Me.ComboBox1.ListCount - 1 If StrComp(Trim$(CStr(Me.ComboBox1.List(i))), enteredValue, vbTextCompare) = 0 Then Exit Sub Next i MsgBox "Choose a value from the list, or add it using the text box." Cancel = True End Sub

4
Test the list and validation

Turn off Developer > Design Mode, switch to another worksheet, and return to load the list. Type a new value in TextBox1 and click Add; then type an unlisted value into ComboBox1 and try to leave the control.

Load and validate the ComboBox with worksheet VBA
Keep the list current: The list reloads when the worksheet is activated and after the Add button is used. If you edit the table directly, switch away from the sheet and return to refresh the ComboBox. Blank ComboBox entries are allowed by this example.
Free Microsoft Office alternative

Manage spreadsheet lists with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for editing table data and moving between common Office formats. It does not fix Excel ActiveX controls; check macro and control compatibility before migrating a workbook that depends on them.

Edit and organize spreadsheet lists in a familiar interfaceOpen and save Microsoft Excel formats, including XLSXUse a free, lightweight Office alternativeMove ordinary spreadsheet data between WPS and Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Why use a separate text box for new values?

The ComboBox BeforeUpdate event rejects unlisted text when the user leaves the control. Entering new values in TextBox1 lets the Add button validate and append them without conflicting with that rule.

How do I change which table supplies the list?

In the worksheet code, replace Table1 in Me.ListObjects("Table1") with your table's name. The example reads ListColumns(1), so change that index if the values are in another column.

What happens if a value is already in the table?

The Add button checks the first column before inserting a row. If it finds a match, ignoring letter case and surrounding spaces, it displays a message and does not add a duplicate.

Why doesn't the ComboBox update after I edit the table directly?

This example reloads the list when the worksheet is activated or the Add button is clicked. Switch to another worksheet and return after making direct table edits.

Can I use this ActiveX solution in Excel for the web or on a Mac?

No. Worksheet ActiveX controls require supported desktop Excel on Windows. Excel for the web and Excel for Mac do not run this worksheet ActiveX solution.