Home > Macros > Basics > Combo Boxes

Excel Worksheet Combo Box Macros

Click a button on the worksheet, and a macro runs, to select the next item in the Combobox list. It's quicker than clicking the arrow, and selecting from the drop down list! Or, run macros to list all the combo buttons on a worksheet, with details about each combo box, for workbook troubleshooting.

Author: Debra Dalgleish

Excel Combo Box Types

When I'm making drop down lists in Excel, most of the time I use the data validation feature. That creates an in-cell drop down, like the list of months in the screen shot below.

Sometimes, if I want a larger font, or more visible items in the drop down, I use a Combo Box instead. In the screen shot below, you can see a data validation cell, and below that there are two types of Combo Boxes:

• Form Control Combo Box
• ActiveX Control Combo Box

In the sections below, there are macros for those two types of Combo Boxes

-- 1) ActiveX Combo Box Macros

-- 2) Form Control Combo Box Macros

Video: Compare 3 Excel Drop Down List Types

This short video shows the 3 types of Excel drop down lists, and the steps for setting up the ActiveX combo box, and the Form Control combo box.

There are written steps on the Drop Down Lists for Data Entry page.

Video Timeline

• 0:00 Introduction
• 0:20 Data Validation Drop Down
• o:58 Form Control Combo Box
• 1:28 Format the Combo Box
• 2:21 ActiveX Combo Box
• 2:42 Set Combo Box Properties
• 3:48 Exit Design Mode
• 4:20 Compare Drop Down Features

1) ActiveX Combo Box Macros

Several of my Excel sample files use ActiveX comboboxes with data validation. Click on a cell that has a drop down list, and a combobox appears, showing the items from the selected cell’s drop down.

For example, click here to see the combobox example that works with named ranges.

There are two Activex Combo Box macros in the sections below.

-- a) List all Activex Combo Boxes on active sheet

-- b) Scroll through Combo Box list items

List All ActiveX Combo Boxes

This macro lists all of the ActiveX combo boxes on the active worksheet, with details about each combo box. If there are other types of ActiveX controls on the worksheet, those are not included in the list.

In the sample workbook, there is a "List Combos" button on the worksheet, and it runs the macro named ListAllCombosAX (the VBA code is shown in the next section)

Combo Box Details List

The screen shot below shows the first 4 columns in the list that the ListAllCombosAX macro creates, on a new sheet in your workbook.

In the sample file, there are 3 combo boxes on the worksheet, and you can see the details for each combo box in the list.

List Combo Boxes - Excel VBA Code

Copy this macro code to the workbook where you want to create a list of ActiveX combo boxes.

Paste the code into a regular code module.

Sub ListAllCombosAX()
  Dim wsA As Worksheet
  Dim wb As Workbook
  Dim wsComboList As Worksheet
  Dim tbl As ListObject
  Dim lCount As Long
  Dim arrCombo As Variant
  Dim cmb As OLEObject
  Dim cmbCount As Long
  Dim i As Long
  Dim rspDel As VbMsgBoxResult
     
  'Count activeX comboboxes on active sheet
  Set wb = ThisWorkbook
  Set wsA = ActiveSheet
  cmbCount = wsA.OLEObjects.Count
  
  'If count is zero, exit macro
  If cmbCount = 0 Then
    MsgBox "No ComboBoxes on this sheet"
    Exit Sub
  End If
  
  'add new sheet named ComboList
  'Check if sheet already exists
  On Error Resume Next
  Set wsComboList = wb.Worksheets("ComboList")
  'If it exists, ask if it should be deleted
  If Err.Number = 0 Then
    rspDel = _
      MsgBox("ComboList sheet already exists" _
      & vbCrLf _
      & "Delete it and continue?", _
          vbYesNo + vbQuestion)
      If rspDel = vbNo Then
        MsgBox "Cancelled"
        Exit Sub
      Else
        'Suppress warnings
        Application.DisplayAlerts = False
        wsComboList.Delete
        Application.DisplayAlerts = True
      End If
  End If
  On Error GoTo 0
  
  Set wsComboList = wb.Worksheets.Add
  wsComboList.Name = "ComboList"
  
  'Add list headings
  wsComboList.Range("A1:F1").Value _
    = Array("Name", "Index", "LinkCell", _
        "TopLeft", "List", "Sel")
  
  'Step 5: row number on list sheet
  lCount = 2
  
  'Loop through comboboxes
  For Each cmb In wsA.OLEObjects
    If TypeName(cmb.Object) = "ComboBox" Then
      'create array with combo details
      arrCombo = Array(cmb.Name, cmb.Index, _
        cmb.LinkedCell, _
          cmb.TopLeftCell.Address, _
          cmb.ListFillRange, cmb.Object.Value)
      'add details to list sheet
      wsComboList.Cells(lCount, 1) _
        .Resize(1, 6).Value = arrCombo
      
      'Add 1 to  row Count
      lCount = lCount + 1
    End If
  Next cmb
  
  'convert list to a named Excel table
  Set tbl = wsComboList.ListObjects _
    .Add(xlSrcRange, wsComboList.Range("A1") _
      .CurrentRegion, , xlYes)
  tbl.Name = "ComboBoxList"
  
  'Autofit all columns in table
  tbl.Range.Columns.AutoFit
  tbl.TableStyle = "TableStyleLight9" 'blue
  
  MsgBox "List is done!"
    
End Sub

Scroll Through ActiveX Combo Box Items

In the sample workbook, there is a combo box that shows a list of weekday names. The combo box is linked to cell C7, where the selected weekday name appears.

In cells C9 and C11, SUMIF formulas calculate the totals for the selected day, based on the entries in the Sales Orders table.

The "Next Day" button runs a macro, named ChangeComboValue, that selects the next day, in the combo box. This button makes it easy to quickly scroll through the list of weekdays, to see each weekday's totals.

This screen shot shows the first four columns in the Sales Order table. In column B, the following formula calculates the weekday name:

• =TEXT([@Date],"dddd")

See more TEXT function examples on the Combine Cells in Excel page.

Scroll Combo Box Items - Excel VBA Code

Copy this macro code to the workbook where you want to scroll through the items in aForm Control combo box, then paste the code into a regular code module.

Modify the Code

In the code, there are references to the sheet name (ComboBox), and the combo box name (ComboDay).

• To use the code in a different workbook, be sure to change those references, to match the names in your own workbook

Sub ChangeComboValue()
  Dim cboDay As OLEObject
  Dim wsCB As Worksheet
  Dim lListCount As Long
  Dim lListGo As Long
  
  Set wsCB = Sheets("ComboBox")
  Set cboDay _
    = wsCB.OLEObjects("ComboDay")
  
  With cboDay.Object
    lListCount = .ListCount
    If .ListIndex _
        = .ListCount - 1 Then
      lListGo = 0
    Else
      lListGo = .ListIndex + 1
    End If
    .Value = .List(lListGo)
  End With

End Sub

2) Form Control Combo Box Macros

Instead of using data validation, or an ActiveX combo box, you could use a Form Control Combo Box. You can't change its font or font size, but you can increase the number of visible rows.

• Note: This type of combo box can be used in Excel for Macintosh, in versions where ActiveX controls are not allowed.

There are two Form Control Combo Box macros in the sections below.

-- a) List all Form Control Combo Boxes on active sheet

-- b) Scroll through Combo Box list items

a) List All Form Control Combo Boxes

This macro lists all of the form control combo boxes on the active worksheet, with details about each combo box.

In the sample workbook, there is a "List Combos" button on the worksheet, and it runs the macro named ListAllDropDowns (VBA code is shown in the next section)

This screen shot shows the first 5 columns in the list that the ListAllDropDowns macro creates, on a new sheet in your workbook.

In the sample file, there are 3 combo boxes on the worksheet, and you can see the details for each combo box in the list..

List Combo Boxes - Excel VBA Code

Copy this macro code to the workbook where you want to create a list of Form Control combo boxes.

Paste the code into a regular code module.

Sub ListAllDropDowns()
'code for form control combo box
  ''called DropDowns in VBA
Dim wsList As Worksheet
Dim wsA As Worksheet
Dim dd As DropDown
Dim ddList As ListObject
Dim lRow As Long
Dim ldds As Long
Dim lNum As Long
Dim lCount As Long
Dim LastCol As Long
Dim strdd As String
Dim nm As Name
Dim nmRng As Range
Dim nmSh As String
Dim strSel As String

On Error Resume Next
Set wsA = ActiveSheet
lRow = 1
LastCol = 8
ldds = wsA.DropDowns.Count

If ldds = 0 Then
  MsgBox "No Form Control combo box on sheet"
  Exit Sub
End If

Set wsList = Sheets.Add
With wsList
  .Range(.Cells(lRow, 1), _
      .Cells(lRow, LastCol)).Value _
    = Array("Name", "Index", "Linked Cell", _
              "TopLeft", "List", "List Sht", _
              "Item", "Sel")
  lRow = lRow + 1

  For Each dd In wsA.DropDowns
    lCount = lCount + 1
    If lCount > ldds Then Exit For
    
    strdd = dd.Name
    Set dd = wsA.DropDowns(strdd)

    Set nm = ThisWorkbook.Names(dd.ListFillRange)
    Set nmRng = nm.RefersToRange
    nmSh = nm.RefersToRange.Parent.Name
    lNum = dd.ListIndex
    If lNum = 0 Then
      strSel = "No Selection"
    Else
      strSel = nmRng.Cells(dd.ListIndex, 1).Value
    End If
      
    If Not dd Is Nothing Then
      .Range(.Cells(lRow, 1), _
        .Cells(lRow, LastCol)).Value _
        = Array(dd.Name, dd.Index, dd.LinkedCell, _
            dd.TopLeftCell.Address, _
            dd.ListFillRange, nmSh, _
            dd.ListIndex, strSel)
      lRow = lRow + 1
    End If
  Next dd

  'change list to Excel table
  Set ddList = .ListObjects.Add(xlSrcRange, _
    .Range("A1").CurrentRegion, , xlYes)
  ddList.TableStyle = "TableStyleLight8"
  ddList.DataBodyRange.CurrentRegion.Columns.AutoFit

End With

End Sub

b) Scroll Through Combo Box Items

In the sample workbook, there is a combo box that shows a list of weekday names. The combo box is linked to cell C7, where a number appears.

In cells C9 and C11, SUMIF formulas calculate the totals for the selected day, based on the entries in the Sales Orders table.

The "Next Day" button runs a macro that selects the next day, in the combo box. This makes it easy to quickly scroll through the weekdays, to see the weekday totals.

This screen shot shows the first four columns in the Sales Order table. In column B, the following formula calculates the weekday number:

• =WEEKDAY(A2,1)

See more date functions on the Excel Date Function Examples page.

Scroll Combo Box Items - Excel VBA Code

Copy this macro code to the workbook where you want to scroll through the items in aForm Control combo box, then paste the code into a regular code module.

Modify the Code

In the code, there are references to the sheet name (ComboFC), and the combo box name (DaysDD).

• To use the code in a different workbook, be sure to change those references, to match the names in your own workbook

Sub ChangeComboValueFC()
'code for form control combo box
'selects next item in list
' after last item, goes to top of list
Dim ddTest As DropDown
Dim wsCB As Worksheet
Dim lListCount As Long
Dim lListGo As Long

Set wsCB = Sheets("ComboFC")
Set ddTest = wsCB.DropDowns("DaysDD")

With ddTest

  lListCount = .ListCount
  If .ListIndex = .ListCount Then
    lListGo = 1
  Else
    lListGo = .ListIndex + 1
  End If
  .ListIndex = lListGo
End With

End Sub

Download Sample Files

• ActiveX Combobox Macros: Download the sample workbook with ActiveX combo box macros, to list all combo boxes, and to scroll through combo box items. The zipped Excel file is in xlsm format, and contains macros. Be sure to enable macros, if you want to test the code.
• Form Control Combobox Macros: Download the sample workbook with Form Control combo box macros, to list all combo boxes, and to scroll through combo box items. The zipped Excel file is in xlsm format, and contains macros. Be sure to enable macros, if you want to test the code.
• Drop Down Types: To test the different drop down list types, you can download the Excel Drop Down Lists sample workbook. The file is in xlsx format, and is zipped. There are no macros in the file.