Dependent Combo Box in Excel

You can add a drop down list in a worksheet cell, so people can select from a list of valid entries. The font in that list is pretty small though, even if the worksheet is at 100% zoom. And good luck trying to read it, if you go to a lower zoom setting! My eyes aren’t that good, so I use a pop-up combo box, in some workbooks, to make the list easier to use.

Combo Box Advantages

On the worksheet, there’s only one combo box, and it pops up when you click or double-click a cell that has a data validation drop down list.

datavalcomboclick01

There are a few benefits to using combo boxes:

  • You can change the font size, so the text is big enough to read
  • You can change the number of visible items, to reduce the amount of scrolling that’s needed
  • When you start typing, the matching item is filled in (autocomplete)

Combo Box Disadvantages

Of course, there are some disadvantages too, so keep those in mind if you decide to use a combo box popup

  • This technique uses programming, and wipes out Excel’s Undo list, like most macros do
  • The single-click option for the code runs every time you select a different cell on the worksheet, so that could slow down a large workbook
  • The combo boxes are ActiveX controls, so they won’t work in Excel for the Mac

Dependent Combo Box

The previous sample files that I’ve uploaded were designed to work with a simple list of items on the same sheet, or a list in a named range on any sheet. They didn’t work with a dependent list though, such as a list of cities based on the region selected in a different cell.

In the screen shot below, column C has data validation that uses a simple INDIRECT formula, =INDIRECT(B3),

If you tried the combo box with that type of data validation list, it just showed an empty list. So sad!

datavalcombodepend01

New and Improved Dependent Combo Box

To prevent the heartbreak of empty combo box lists, I finally uploaded a new combo box sample file. It doesn’t handle fancy formulas, but at least is shows a drop down list if a cell has a simple INDIRECT formula.

datavalcombodepend02

Download the Dependent Combo Box File

You can learn more about the Dependent Excel Combo Box on my website, and download the sample file.

Data Entry Popup Kit

If you have drop down lists with complex dependent formulas, there is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium. It runs on a protected worksheet, and works with most types of dependent drop down lists.

In the premium version, a list box automatically appears when you select a cell that has a drop down list. You can set it to allow selection of a single item, or multiple items. The kit has a setup sheet, that lets you quickly customize the list box, and simple steps to add this technique to your own workbooks.

____________________

Save

You may also like...

4 Responses

  1. Ralf den Ouden says:

    Hello,
    I have used the technic as described above and it works fine however I have some dependent drop down lists with an offset function as well were I can’t get it working. Is it possible to modify the additional piece of vba code to get it working with the offset function? I have used the offset technic which was also desecribed on contextures.com. Or do I have to use the Data Entry Popup Kit? I don’t need all the functions from that (what I saw in the movie) to get my file running.

    Best regards,
    Ralf

    • @Ralf, the Data Entry Popup kit works with most OFFSET formulas for dependent data validation. It has a listbox on a UserForm, and that has some features that aren’t available in a worksheet combo box.

  2. Andrew says:

    Hi,

    I am after the same adaptation to the code so the pop up combo box works with dependent validation lists with the OFFSET function.
    ie =OFFSET(Lists!$B$42,1,0,COUNTA(Lists!$B$42:$B$47)-1,1)
    and =OFFSET(Lists!$D$42,MATCH(J3,Lists!$D$42:$D$56,0)-1,1,COUNTIF(Lists!$D$42:$D$56,J3),1)

    I have adapted the original VBA code to work when you tab or (single) click into a cell with data validation and the combo box pops up, LOVE your code, so useful and easy enough instructions to adapt to suit!!

    Thanks
    Andrew

    • @Andrew, thanks, I’m glad you like the code! As I said to Ralf (above), the Data Entry Popup kit works with most OFFSET formulas for dependent data validation. It has a listbox on a UserForm, and that has some features that aren’t available in a worksheet combo box.

Leave a Reply to Ralf den Ouden Cancel reply

Your email address will not be published. Required fields are marked *