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.

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Loop Through Spreadsheet Option Buttons

What This VBA Code Does Use can use this loop to figure out which one of your Option Buttons is...

Loop Through All Form Control Checkboxes On Excel Spreadsheet

What This VBA Code Does This code will loop through each of your Form Control checkboxes and let you 'do...

VBA to Populate Userform Combobox from Excel Table

What This VBA Code Does The below code will show you how to utilize an Excel Table Object to populate...

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.