Author: Oscar Cronquist Article last updated on May 19, 2022


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

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.

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

Watch this video about Combo Boxes

Create a combobox using vba

create-combobox1

Sub CreateFormControl()

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

End Sub

Recommended article

Recommended articles

test
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 […]

Back to top

Assign a macro - Change event

You can assign a macro to a combobox by press with right mouse button oning 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 rund as soon as the selected value in the combo box is changed.

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

Recommended article

Recommended articles

test
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

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

Recommended article

Recommended articles

test
How to automatically add new items to a drop down list
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]

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.

Recommended article

Recommended articles

test
How to use DIALOG BOXES
A dialog box is an excellent alternative to a userform, they are built-in to VBA and can save you time […]

Back to top

Set the default 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

Recommended article

Recommended articles

test
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 […]

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

Recommended article

Recommended articles

test
Working with TEXT BOXES [Form Controls]
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]

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

Recommended article

Recommended articles

test
Working with TEXT BOXES [Form Controls]
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]

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 press with right mouse button oning the combo box and then press with left mouse button on "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 rund 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

COMBO BOXES [Form Controls] examples

test
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
test
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
test
How to change a picture in a worksheet dynamically [VBA]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
test
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
test
How to use the Scroll Bar
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
test
Locate a shape in a workbook
This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]
test
Move a shape [VBA]
This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape […]
test
Multi-level To-Do list template
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
test
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 […]
test
Populate a list box with unique distinct values from a filtered Excel table [VBA]
Excel defined Tables, introduced in Excel 2007, sort, filter and organize data any way you like. You can also format […]
test
Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
test
Toggle a macro on/off using a button
This article demonstrates how the user can run a macro by press with left mouse button oning on a button, […]
test
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 […]
test
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 […]
test
Working with TEXT BOXES [Form Controls]
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.