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

I am using excel 2007.

Create a combo box (form control)

  1. Click Developer tab on the ribbon. How to show developer tab
  2. Click Insert button.
  3. Click Combo box
  4. Create a combo box on a sheet.
    (Click 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. Left click 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.

Sub FilterUniqueData()
    Dim Lrow As Long, test As New Collection
    Dim Value As Variant, temp() As Variant
    ReDim temp(0)

    On Error Resume Next
    With Worksheets("Sheet1")
        Lrow = .Range("A" & Rows.Count).End(xlUp).Row
    temp = .Range("A2:A" & Lrow).Value
    End With

    For Each Value In temp
        If Len(Value) > 0 Then test.Add Value, CStr(Value)
    Next Value

    Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat.RemoveAllItems

    For Each Value In test
         Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat.AddItem Value
    Next Value

    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. Right click combobox and click "Assign Macro...". See picture below.

This means when you click 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 click 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

Download excel 2007 macro enabled workbook *.xlsm

Populate combo box.xlsm