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 as ActiveX controls but are compatible with earlier versions of Excel. You can find the controls on the developer tab.

Watch this video about Combo Boxes

1. 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

Recommended article

Recommended articles

Back to top

2. 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

Back to top

3. 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

4. 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

Back to top

5. 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

Back to top

6. 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

Back to top

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

Back to top

8. 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

9. 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

10. Populate combox with values from a table

Sub PopulateFromTable()

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

End Sub

Back to top

11. 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

12. Populate a combo box (form control)

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

12.1 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.

12.2 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.

12.3 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

12.4 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

13. 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 an Excel defined Table
• Populate a combobox with unique values using a PivotTable

13.1 Populate a combobox with table headers

In the picture below you can see a table and two combo boxes. The table has about 50 000 rows.

Let's populate the combo box to the left with table headers, see image above.

'Name macro
Sub FillCombo1()

'Declare variables and data types
Dim Cell As Range
Dim i As Single
Dim shpobj As Object

'Assign Table1 to object Cell
Set Cell = Worksheets("Sheet1").Range("Table1")

'Clear values in ComboBox1 
Worksheets("Sheet1").ComboBox1.Clear

'Go through each column in object Cell (Table1)
For i = 1 To Cell.ListObject.Range.Columns.Count

    'Add column header value to combobox
    Worksheets("Sheet1").ComboBox1.AddItem Cell.ListObject.Range.Cells(1, i)

'Continue with next column
Next i

End Sub

13.2 Populate a combo box with unique values using a Pivot Table

The selected value in the combobox to the left determines which column is going to be filtered in the PivotTable.

The combobox to the right is populated when the pivottable has processed all values. I think you get the idea when you see the picture below.

'Name macro
Sub FillCombo2()

'Declare variables and data types
Dim i As Single, r As Single
Dim shpobj As Object
Dim rng As Variant, Value As Variant

'Don't show changes
Application.ScreenUpdating = False

'Refresh PivotTable
Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache.Refresh

'Ignore errors
On Error Resume Next

'Hide all fields in PivotTable
For Each ptfl In Worksheets("Sheet2").PivotTables("PivotTable1").PivotFields
    ptfl.Orientation = xlHidden
Next ptfl

'Don't ignore errrors
On Error GoTo 0

'Go through all OLEObjects (ActiveX) in Sheet1
For Each shpobj In Worksheets("Sheet1").OLEObjects

    'Find ComboBox1 and make sure it is not empty if so exit For ... Next statement
    If shpobj.Name = "ComboBox1" And shpobj.Object.Value = "" Then Exit For
    
    'Use the value in ComboBox1 to extract unique distinct values in PivotTable
    If shpobj.Name = "ComboBox1" Then
        Worksheets("Sheet2").PivotTables("PivotTable1") _
        .PivotFields(shpobj.Object.Value).Orientation = xlRowField
        Worksheets("Sheet2").Select

        'Make sure that the Pivot table is recalculated
        Application.Calculate
    End If
    
    'Check if shpobj is Combobox2
    If shpobj.Name = "ComboBox2" Then
         
        'Clear comboobox values
        shpobj.Object.Clear

        'Populate ComboBox2 with values from PivotTable
        shpobj.Object.List = Worksheets("Sheet2").PivotTables("PivotTable1").RowRange.Value
        shpobj.Object.RemoveItem 0
        shpobj.Object.RemoveItem _
        Worksheets("Sheet2").PivotTables("PivotTable1").RowRange.Rows.Count - 2
    End If
Next shpobj

'Activate Sheet1
Worksheets("Sheet1").Select

'Show changes for the user
Application.ScreenUpdating = True

End Sub

The pivot table is located on sheet2. Sheet1 module also contains some code:

Private Sub ComboBox1_Change()

Call FillCombo2

End Sub

13.3 How to add code to sheet module

1. Go to VB Editor (Alt + F11)
2. Double press with left mouse button on Sheet4 (Sheet1) in Project Explorer, see image above. This is the sheet where ComboBox1 is located.
3. Paste code to code module.

14. Apply dependent combo box selections to a filter

Answer:

Table of contents

1. Overview
2. Create auto expanding named ranges
3. Array Formula on Calculation sheet
4. Create another two auto expanding named ranges on sheet "Calculation"
5. Create Combo boxes
   1. Setup Combo boxes
6. Array Formula on Filter sheet
7. Get example workbook

14.1. Overview

Filter or an excel table can accomplish this task within milliseconds but several people have asked how to do this using drop down lists.

So in this post I am going to use dependent combo-boxes instead of drop down lists.We are not going to use any vba code but we need to create a "calculation" sheet.

Why use combo box? If  you decide to change a value in a  combo box, other dependent combo box values change instantly. This is not the case with drop down lists.

We are going to create two combo boxes. The first combo box contains unique distinct values from column A. The second combo box contains unique distinct values from column B, based on selected value in the first combo box. See pictures below.

The data we are working with:

The final result:

If you want to create drop down lists read this article: Create dependent drop down lists containing unique distinct values in excel and then continue reading this article.

What is a unique distinct list? Remove all duplicates in a list and you have create a unique distinct list.

I have created three sheets.

• Filter
• Data
• Calculation
  

Back to top

14.2.Create auto expanding named ranges

We are going to create two named ranges, auto expanding when new values are added. The two named ranges exists on sheet "Data"

Named range "order"

1. Press with left mouse button on "Formulas" tab
2. Press with left mouse button on "Name Manager"
3. Press with left mouse button on "New..."
4. Type a name. I named it "order". (See attached file at the end of this post)
5. Type =OFFSET(Data!$A$3, 0, 0, COUNTA(Data!$A$3:$A$1000)) in  "Refers to:" field.
6. Press with left mouse button on "Close" button

Named range "product"

1. Press with left mouse button on "Formulas" tab
2. Press with left mouse button on "Name Manager"
3. Press with left mouse button on "New..."
4. Type a name. I named it "product". (See attached file at the end of this post)
5. Type =OFFSET(Data!$B$3, 0, 0, COUNTA(Data!$B$3:$B$1000)) in  "Refers to:" field.
6. Press with left mouse button on "Close" button

Back to top

14.3. Array Formula on sheet "Calculation"

Array formula in cell A2:

Press CTRL + SHIFT + ENTER. Copy cell A2 and paste it down as far as needed.

Array formula in cell B2:

Press CTRL + SHIFT + ENTER. Copy cell B2 and paste it down as far as needed.

The values in column B change depending on selected value in the combo box on sheet "Filter". Don´t worry, we are soon going to create the combo boxes.

Back to top

14.4. Create another two auto expanding named ranges on sheet "Calculation"

The named ranges auto expands when lists change in column A and B.

Named range "uniqueorder"

1. Press with left mouse button on "Formulas" tab
2. Press with left mouse button on "Name Manager"
3. Press with left mouse button on "New..."
4. Type a name. I named it "uniqueorder". (See attached file at the end of this post)
5. Type =OFFSET(Calculation!$A$2,  0,  0,  COUNT(IF(Calculation!$A$2:$A$1000="",  "",  1)),  1) in  "Refers to:" field.
6. Press with left mouse button on "Close" button

Named range "uniqueproduct"

1. Press with left mouse button on "Formulas" tab
2. Press with left mouse button on "Name Manager"
3. Press with left mouse button on "New..."
4. Type a name. I named it "uniqueproduct". (See attached file at the end of this post)
5. Type =OFFSET(Calculation!$B$2,  0,  0,  COUNT(IF(Calculation!$B$2:$B$1000="",  "",  1)),  1) in  "Refers to:" field.
6. Press with left mouse button on "Close" button

Back to top

14.5. Create Combo boxes

1. Press with left mouse button on "Developer" tab on the ribbon
2. Press with left mouse button on "Insert" button
3. Press with left mouse button on Combo box button
4. Place combo box on cell B2 on sheet "Filter

 

Create a second combo box on cell C2.

 

Back to top

14.5.1 Setup Combo box 1

1. Select sheet "Filter"
2. Press with right mouse button on on combo box on cell B2
3. Press with left mouse button on "Format Control..."
4. Press with left mouse button on tab "Control"
5. Type uniqueorder in Input range:
6. Select cell B1 in Cell link:
7. Press with left mouse button on OK

Setup Combo box 2

1. Select sheet "Filter"
2. Press with right mouse button on on combo box on cell C2
3. Press with left mouse button on "Format Control..."
4. Press with left mouse button on tab "Control"
5. Type uniqueproduct in Input range:
6. Select cell C1 in Cell link:
7. Press with left mouse button on OK

I have hidden the values in cell B1 and C1.

1. Select B1:C1
2. Press Ctrl + 1
3. Press with left mouse button on "Custom" in Category window
4. Type ,,, in the "Type:" field
5. Press with left mouse button on OK

Back to top

14.6. Array Formula on "Filter" sheet

Array formula in cell B7:

Press CTRL + SHIFT + ENTER. Copy cell B7 and paste it to the right to cell B9. Copy cell range B7:B9 and paste it down as far as needed.

Back to top

Combobox category

Form controls category

Macro category

Vba category

More than 1300 Excel formulas

Excel categories

Comments (46)