Populate a combo box (form control) [VBA]

In this tutorial I am going to explain how to:

• Create a combo box (form control)
• Filter unique values and populate a combo box (form control)
• Copy selected combo box value to a cell
• Refresh combo box using change events

Create a combo box (form control)

1. Press with left mouse button on Developer tab on the ribbon. How to show developer tab
2. Press with left mouse button on Insert button.
3. Press with left mouse button on Combo box
4. Create a combo box on a sheet.
   (Press and hold left mouse button on a sheet. Drag down and right. Release left mouse button.)

The question now is how do you know the name of a combo box? You have to know the name when you are writing the vba code. Press with left mouse button on a combo box and the name appears in the name box.

Filter unique values and populate a combo box (form control)

The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.

'Name macro
Sub FilterUniqueData()

'Declare variables and data types
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant

'Redimension array variable temp in order to let it grow when needed
ReDim temp(0)

'Ignore errors
On Error Resume Next

'Find last not empty cell in column A
With Worksheets("Sheet1")
  Lrow = .Range("A" & Rows.Count).End(xlUp).Row

  'Populate array temp with values from column A
  temp = .Range("A2:A" & Lrow).Value
End With

'Iterate through values in array variable temp
For Each Value In temp

   'Add value if character length is larger than 0 to test
    If Len(Value) > 0 Then test.Add Value, CStr(Value)

'Continue with next value
Next Value

'Delete all items in Combobox Drop Down 1 in sheet1
Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat.RemoveAllItems

'Populate combobox with values from test one by one
For Each Value In test
   Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat.AddItem Value
Next Value

'Clear variable
Set test = Nothing
End Sub

Copy the code into a standard module.

Copy selected combo box value to a cell

You can assign a macro to a combobox. Press with right mouse button on combobox and press with left mouse button on "Assign Macro...". See picture below.

This means when you press with left mouse button on the combobox the selected macro is run.

The vba code below copies the selected value to cell C5 whenever the combobox is selected.

1. Copy the code into a standard module.
2. Assign this macro to the combobox .

Sub SelectedValue()
With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat
   Worksheets("Sheet1").Range("C5") = .List(.Value)
End With
End Sub

Refresh combo box using change events

The next step is to make the combo box dynamic. When values are added/edited or removed from column A, the combo box is instantly refreshed. Copy code below Press Alt + F11 Double press with left mouse button on sheet1 in project explorer Paste code into code window

Private Sub Worksheet_Change(ByVal Target As Range) 
If Not Intersect(Target, Range("$A:$A")) Is Nothing Then 
   Call FilterUniqueData 
End If 
End Sub

Get the Excel file

Populate-combo-box.xlsm

Combobox category

Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Populate a combobox with values from a pivot table [VBA]
In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]

Form controls category

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Create a Print button – macro
This article describes how to create a button and place it on an Excel worksheet, then assign a macro to […]

Working with LIST BOXES (Form Controls)
This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]

Macro category

Save invoice data – VBA
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Excel categories

Comments (10)