This blog post demonstrates how to create, populate and change comboboxes (form control) programatically.

Form controls are not as flexible as ActiveX controls but are compatible with earlier versions of Excel. You can find the controls on the developer tab. How to show the developer tab

Table of Contents

1. Create a combobox using vba
2. Assign a macro - Change event
3. Add values to a combobox
4. Remove values from a combo box
5. Set the selected value in a combo box
6. Read selected value
7. Link selected value
8. Change combobox properties
9. Populate combox with values from a dynamic named range
10. Populate combox with values from a table
11. Currently selected combobox

Create a combobox using vba

Sub CreateFormControl()
'Worksheets("Sheet1").DropDowns.Add(Left, Top, Width, Height)
Worksheets("Sheet1").DropDowns.Add(0, 0, 100, 15).Name = "Combo Box 1"
End Sub

Back to top

Assign a macro - Change event

You can assign a macro to a combobox by right clicking on the combobox and select "Assign Macro". Select a macro in the list and press ok!

This subroutine does the same thing, it assigns a macro named "Macro1" to "Combo box 1" on sheet 1. Macro1 is executed as soon as the selected value in the combo box is changed.

Sub AssignMacro()
Worksheets("Sheet1").Shapes("Combo Box 1").OnAction = "Macro1"
End Sub

Back to top

Add values to a combo box

You can add an array of values to a combo box. The list method sets the text entries in a combo box, as an array of strings.

Sub PopulateCombobox1()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.List = _ 
Worksheets("Sheet1").Range("E1:E3").Value
End Sub

The difference with the ListFillRange property is that the combo box is automatically updated as soon as a value changes in the assigned range. You don´t need to use events or named ranges to automatically refresh the combo box, except if the cell range also changes in size.

Sub PopulateCombobox2()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.ListFillRange = _
"A1:A3"
End Sub

You can also add values one by one.

Sub PopulateCombobox3()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
.AddItem "Sun"
.AddItem "Moon"
.AddItem "Stars"
End Sub

Back to top

Remove values from a combo box

RemoveAllItems is self-explanatory.

Sub RemoveAllItems()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.RemoveAllItems
End Sub

The RemoveItem method removes a value using a index number.

Sub RemoveItem()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.RemoveItem 1
End Sub

The first value in the array is removed.
Back to top

Set the selected value in a combo box

The ListIndex property sets the currently selected item using an index number. ListIndex = 1 sets the first value in the array.

Sub ChangeSelectedValue()
With Worksheets("Sheet1").Shapes("Combo Box 1")
.List = Array("Apples", "Androids", "Windows")
.ListIndex = 1
End With
End Sub

Back to top

Read selected value

The ListIndex property can also return the index number of the currently selected item. The list method can also return a value from an array of values, in a combo box. List and Listindex combined returns the selected value.

Sub SelectedValue()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
MsgBox "ListIndex: " & .ListIndex & vbnewline & "List value:" .List(.ListIndex)
End With
End Sub

Back to top

Link selected value

Cell F1 returns the index number of the selected value in combo box 1.

Sub LinkCell()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.LinkedCell = "F1"
End Sub

Cell G1 returns the selected value in combo box 1.

Sub LinkCell2()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
    Worksheets("Sheet1").Range("G1").Value = .List(.ListIndex)
End With
End Sub

Back to top

Change combobox properties

Change the number of combo box drop down lines.

Sub ChangeProperties()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.DropDownLines = 2
End Sub

Back to top

Populate combox with values from a dynamic named range

I created a dynamic named range Rng, the above picture shows how.

Sub PopulateFromDynamicNamedRange()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.ListFillRange = "Rng"
End Sub

You can do the same thing as the subroutine accomplishes by right clicking the combo box and then left click "Format Control...". Input Range: Rng
Back to top

Populate combox with values from a table

Sub PopulateFromTable()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.List = _
[Table1[DESC]]
End Sub

Back to top

Currently selected combobox

It is quite useful to know which combo box the user is currently working with. The subroutine below is assigned to Combo Box 1 and is executed when a value is selected. Application.Caller returns the name of the current combo box.

Sub CurrentCombo()
MsgBox Application.Caller
End Sub

Back to top

Related posts:

Excel vba: Populate a combo box (form control)

Populate a list box with visible unique values from an excel table (vba)

Change chart data range using a drop down list (vba)

Apply dependent combo box selections to a filter in excel 2007

Excel vba: Populate a combobox with values from a pivot table