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
Create a combobox using vba
Assign a macro - Change event
Add values to a combobox
Remove values from a combo box
Set the selected value in a combo box
Read selected value
Link selected value
Change combobox properties
Populate combox with values from a dynamic named range
Populate combox with values from a table
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 SubBack 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 SubBack 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 SubThe 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 SubYou can also add values one by one.
Sub PopulateCombobox3()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
.AddItem "Sun"
.AddItem "Moon"
.AddItem "Stars"
End SubBack to top
Remove values from a combo box
RemoveAllItems is self-explanatory.
Sub RemoveAllItems()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.RemoveAllItems
End SubThe RemoveItem method removes a value using a index number.
Sub RemoveItem()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.RemoveItem 1
End SubThe 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 SubBack 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 SubBack 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 SubCell 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 SubBack 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 SubBack 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 SubYou 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 SubBack 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
January 4th, 2012 at 10:47 am
Thank you very much. This solution has worked nicely.
Now, say if I have 1000 names alltogether. I guess I'll have to copy the array formula upto cell A1001. Also, I will have to increase the $A$500 upto $A$1001 in the Named Range Formula. Is that right?
Thanks again.
January 4th, 2012 at 5:27 pm
Hey Oscar,
I have done a slight change in my workbook. In Sheet:Calc, I have made a table in Column A and named it tblAllNames. Then I copied the array formula in its first row and adjusted the table rows as far as I needed (by pulling down the bottom right corner of the table). This way the formula copies itself in all the rows. Also, I can add names, which don’t exist in either of the Customer or Vendor tables, such as Bank Name or Employee Name, etc. I do have to remember not to sort this table because I tried that and it messed up things.
Then after this I created a Named range called AllNames and simply replaced the range by table name.
In its formula box I typed: =OFFSET(tblAllNames, 0, 0, MAX(IF(tblAllNames"", ROW(tblAllNames), "A"))-1).
This worked fine.
Can you please explain how this formula works? Why do you have “A” in it?
By the way, what if I make another table called tblEmployee and want to add all employee names to my drop down list as well . I’m sure a lot of things are going to change in the array formula.
January 6th, 2012 at 9:39 am
Rattan,
In its formula box I typed: =OFFSET(tblAllNames, 0, 0, MAX(IF(tblAllNames"", ROW(tblAllNames), "A"))-1).
This worked fine.
Can you please explain how this formula works? Why do you have “A” in it?
You can also use "". The "A" is just a random letter. Max function handles only numerical values, remaining values are ignored.
Explaining the named range formula
The formula gets all values in tblAllNames. I can´t use counta function in this case.
Step 1 - Convert nonempty values to row numbers
IF(tblAllNames<>"", ROW(tblAllNames), "A")
Step 2 - Get the largest row number
MAX(IF(tblAllNames<>"", ROW(tblAllNames), "A"))
Step 3 - Get values from cell range
=OFFSET(tblAllNames, 0, 0, MAX(IF(tblAllNames<>"", ROW(tblAllNames), "A"))-1)
By the way, what if I make another table called tblEmployee and want to add all employee names to my drop down list as well . I’m sure a lot of things are going to change in the array formula.
Change formula in sheet Calc to the formula found in this blog post:
Merge three columns into one list in excel