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

Related articles

Create permutations [UDF]
Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]