Populate an Excel ActiveX ComboBox from a Table with VBA
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.

- 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.
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.
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.
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.
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.
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
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.

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.

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.




