×

VBA For Excel’s Form Control Combo Boxes

By Chris Newman •  Updated: 05/14/14 •  5 min read
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
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.


Keep Learning

Chris Newman

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.

[FREE Training] 10 Amazing Excel Efficiency Tricks. Why Don't People Know These?!

X