Excel ActiveX Combobox Item Select Macro

Someone asked how they could show the items in a worksheet Combobox list, one after the other. It was an interesting challenge, so here is my Activex Combobox item select macro.

Combobox 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. It even shows the list for a simple dependent list, based on the INDIRECT function.

This short video shows how the combobox works with the drop down lists.

Combobox Items List

For a worksheet ActiveX combobox, you can set the ListFillRange property to an address or a range name, to show a specific list of items.

In this screen shot, the combobox will show the months from the named range, ListA.

List Property Problem

In all the years that I’ve worked with ActiveX comboboxes, I’d never tried to loop through the list of items. And in this case, the question was about worksheet comboboxes, which don’t work the same as UserForm comboboxes, so sample code for those wouldn’t work.

After some experimenting and hunting, I found that you can use the Object property for the combobox, and that was the secret to looping through the list of items.

If you try this line of code for the List property, Excel shows an error:

Debug.Print cboTest.List(1)

But, if you include the Object property, the line works:

Debug.Print cboTest.Object.List(1)

Loop Through Combobox Items

With the Object property, I was able to create code that finds the current value in the combobox, and moves to the next value.

The code is stored in a regular code module, and there is a combobox named “TestCombo”, on the worksheet named “ComboBox”.

NOTE: There’s no error checking in this macro, so you can add that if you use this code in your files.

Sub ChangeComboValue()
Dim cboTest As OLEObject
Dim wsCB As Worksheet
Dim lListCount As Long
Dim lListGo As Long

Set wsCB = Sheets("ComboBox")
Set cboTest = wsCB.OLEObjects("TestCombo")

With cboTest.Object
  lListCount = .ListCount
  If .ListIndex = .ListCount - 1 Then
    lListGo = 0
  Else
    lListGo = .ListIndex + 1
  End If
  .Value = .List(lListGo)
End With

End Sub

Get the Sample Workbook

In the sample file, there is a button on the worksheet, above the ActiveX combobox. Click that button, to select the next item in the list.

NOTE: If the combobox is empty, or if you get to the end of the list, the first item is shown.

To get the sample file, go to the Excel Sample Files page, and in the Data Validation section, look for DV0073 -Data Validation Combobox – Select Next Item.

The zipped workbook is in xlsm format, and contains macros. When you open the file, be sure to enable macros, if you want to test the combobox and button.

________________________