VBA For Excel’s Form Control Combo Boxes

You’re VBA Combo Box Cheat Sheet
In this post I am going to share everything I know about using VBA with an Excel Form Control Combo Box (aka drop down). Most of the code is very self-explanatory so I will not write much of a description. However, some of the syntax can be a little tricky so pay close attention to how the code is structured. Please feel free to post comments if I missed an area or you have any questions! Enjoy 🙂
Creating & Sizing/Positioning A Combo Box
Sub ComboBox_Create()
'PURPOSE: Create a form control combo box and position/size it
Dim Cell As Range
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
'Create
sht.DropDowns.Add(0, 0, 100, 15).Name = "Combo Box 1"
'Create & Dimension to a Specific Cell
Set Cell = Range("B5")
With Cell
sht.DropDowns.Add(.Left, .Top, .Width, .Height).Name = "Combo Box 2"
End With
'Create & Dimension to a Specific Cell Range
Set Cell = Range("B8:D8")
With Cell
sht.DropDowns.Add(.Left, .Top, .Width, .Height).Name = "Combo Box 3"
End With
End Sub
Deleting A Combo Box
Sub ComboBox_Delete()
'PURPOSE: Delete a form control combo box
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
sht.Shapes("Combo Box 1").Delete
End Sub
Adding Values To A Combo Box
Sub ComboBox_InputRange()
'PURPOSE: Add values to your drop down list
Dim Cell As Range
Dim sht As Worksheet
Dim myArray As Variant
Dim myDropDown As Shape
Set sht = ThisWorkbook.Worksheets("Sheet1")
Set myDropDown = sht.Shapes("Combo Box 1")
myArray = Array("Q1", "Q2", "Q3", "Q4")
'Based on data in a range (not linked)
MyDropDown.ControlFormat.List = sht.Range("A1:A4").Value
'Linked to data in a range (automatically changes based on current cell values)
myDropDown.ControlFormat.ListFillRange = "A1:A4"
'Based on Array values (written out)
MyDropDown.ControlFormat.List = _
Array("Q1", "Q2", "Q3", "Q4")
'Based on Array values (variable)
myDropDown.OLEFormat.Object.List = myArray
'Add one by one
With myDropDown.ControlFormat
.AddItem "Q1"
.AddItem "Q2"
.AddItem "Q3"
.AddItem "Q4"
End With
End Sub
Overriding Values In The Drop Down List
Sub ComboBox_ReplaceValue()
'PURPOSE: Replace value of the third item in the drop down list
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.List(3) = "FY"
End Sub
Removing Values From The Drop Down List
Sub ComboBox_RemoveValues()
'PURPOSE: Remove a value(s) from the drop down list
Dim Cell As Range
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
'Remove A Single Item
sht.Shapes("Combo Box 1").ControlFormat.RemoveItem 2
'Remove All Items
sht.Shapes("Combo Box 1").ControlFormat.RemoveAllItems
End Sub
Determine Current Selected Value From The Drop Down List
Sub ComboBox_GetSelection()
'PURPOSE: Determine current selected value in ComboBox
Dim sht As Worksheet
Dim myDropDown As Shape
Set sht = ThisWorkbook.Worksheets("Sheet1")
Set myDropDown = sht.Shapes("Combo Box 1")
With myDropDown.ControlFormat
MsgBox "Item Number: " & .Value & vbNewLine & "Item Name: " & .List(.Value)
End With
End Sub
Select A Value From The Drop Down List
Sub ComboBox_SelectValue()
'PURPOSE: Automatically select a value from the drop down list
Dim Cell As Range
Dim sht As Worksheet
Dim Found As Boolean
Dim SetTo As String
Dim x As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
'Select First List Item
sht.Shapes("Combo Box 1").ControlFormat.ListIndex = 3
'Select Item based on list Name/Value
SetTo = "Q2"
With sht.Shapes("Combo Box 1").ControlFormat
For x = 1 To .ListCount
If .List(x) = SetTo Then
Found = True
Exit For
Next x
If Found = True Then .ListIndex = x
End With
End Sub
Link User’s Selection To A Cell (Outputs Numerical List Position)
Sub ComboBox_CellLink()
'PURPOSE: Output the selection's list position to a specific cell
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
sht.Shapes("Combo Box 1").ControlFormat.LinkedCell = "$A$1"
End Sub
Adjust Drop Down Lines For A Combo Box
Sub ComboBox_DropDownLines()
'PURPOSE: Set how many drop down lines are visible per scroll
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
sht.Shapes("Combo Box 1").ControlFormat.DropDownLines = 12
End Sub
Toggle On/Off 3D Shading
Sub ComboBox_3DShading()
'PURPOSE: Turn 3D shading on or off
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
'Turn 3D Shading On
sht.Shapes("Combo Box 1").OLEFormat.Object.Display3DShading = True
'Turn 3D Shading Off
sht.Shapes("Combo Box 1").OLEFormat.Object.Display3DShading = False
End Sub
Assigning A Macro To A Combo Box
Sub ComboBox_AssignMacro()
'PURPOSE: Assign a macro to be triggered when drop down is changed
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
sht.Shapes("Combo Box 1").OnAction = "Macro1"
End Sub
Any Others?
If I’ve missed any VBA functionalities please leave a comment in the comments section below so I can continue to grow this list of combo box code! I look forward to hearing your thoughts.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.