VBA For Excel's Form Control Combo Boxes

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)
  Worksheets("Sheet1").Shapes("Combo Box 1").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)
  Worksheets("Sheet1").Shapes("Combo Box 1").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.

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)