Worksheet Combo Box Problem in Excel 2013

On of my favourite Excel tricks is to show a combo box, when you click a cell that has a drop down list. The combo box can have larger font, and it autocompletes, so it’s easier for people to use.

There is only one ActiveX combo box on the worksheet, and it is hidden until you click on a drop down cell. When it appears, it shows the data validation list for the selected cell.

combo box with drop down list

Combo Box Code

There is code on the worksheet, that automatically runs when you select a cell. It checks to see if the cell has a validation list. If it does, then the code gets the name of the list that is used.

datavalcomboclick02

That name is added to the combo box’s ListFillRange property, so it shows the correct list for the selected cell.

datavalcomboclick03

Excel 2013 Problem

Recently, I discovered a problem with these combo boxes in Excel 2013. A client had upgraded from Excel 2010 to 2013, and the combo boxes didn’t show the lists in the new version.

datavalcomboclick04

The code wasn’t entering the list name in the combo box’s ListFillRange property. When I tried to change the property manually, I couldn’t do that either. Excel just cleared out the property, after I filled it in.

datavalcomboclick05

Create a New Name

The lists were named ranges based on Excel tables. One was named ProdList, and here is its definition:

   =tblProducts[Product]

I thought the table might be causing the problem, so I created a new name, based on the first name:

  1. On the Ribbon’s Formulas tab, click Define Name
  2. In the New Name window, type a one-word name — ProdListB
  3. Click in the Refers to box, and press the F3 key
  4. In the Paste Name window, click on the table-based name — ProdList — and click OK
  5. Click OK to complete the name

comboboxlistfill01

When I tested the new name in the combo box, it worked fine. Excel accepted the name without any problems.

Use Existing Names

If you’ve already got data validation lists set up, it could take a lot of work to change all those lists, to use new names. Also, you might have formulas that refer to the existing names, and you don’t want to change all those formulas, to use a new name.

In that case, create an intermediate name, based on the table, then change the existing name, so it is based on the intermediate name.

Using the example above,

  1. On the Ribbon’s Formulas tab, click Name Manager
  2. Click the ProdList name, select the formula in the Refers To box, and press Ctrl+C to copy it
  3. Click the New button, and type a name for the intermediate formula — ProdList_A
  4. Click in the Refers to box, and press Ctrl+V, to paste the formula, then click OK
    • create intermediate name
  5. Next, click the ProdList name in the Name Manager list
  6. In the Refers To box, change its formula to: =ProdList_A
  7. Click the green check mark, at the left of the Refers To box, to save the change
  8. Click Close, to close the Name Manager

original name based on intermediate name

Don’t Use Table Based Names

So, if you are having a problem with the ListFillRange property in Excel 2013 worksheet combo boxes, it might be caused by table-based names. Try creating another name, based on the first one, and see if that solves the problem.

You can download a sample file with a worksheet combo box, to see the names that I created, as a workaround for this problem

NOTE: This also affects the Forms control combo boxes in Excel 2013. Both types of combo boxes work with table based names in Excel 2010, and UserForm combo boxes in both versions work with table based names.

Remember the Zoom Settings

And while we’re talking about combo box problems, remember to keep the list sheet at 100% zoom, to prevent crashes when you click on a combo box on the data entry sheet.

In the survey that I have in that blog post, 26 people said “Yes”, a combo box had crashed on them, and only 2 said “No.”

In the next version of Excel, we might have to stand on one leg, and wear a pointy hat, if we want combo boxes to work! But I’ll probably keep using them.

comboboxcrash01

_________________

You may also like...

12 Responses

  1. Frank says:

    Debra,
    fyi
    running the latest version of Excel 2013 I do not have these problems, neither with ProdList nor with zooming ValidationLists.

  2. Serge says:

    Still have this problems with Excel 32-bit version 2013 15.0.4569.1504.

    I have found another possible solution for this issue.

    We can’t use ListFillRange with table-based range, but with some simple VBA we can find this range .Address property, and set it as combo .ListFillRange value (probably with sheet name prependedd).

    I.e., if .ListFillRange= “TableRange” doesn’t work, we can use something like:

    .ListFillRange= Sheet1.Range(“TableRange”).Address

  3. Dash says:

    Hey Serge,
    How would you do it if the cells where data validation is, different cells have a different data validation?

    • @Dash, I’ve added a new section — Use Existing Names
      It explains how to set up intermediate names, and change the original names to be based on those.
      That way, you won’t have to change any existing data validation formulas.

  4. Benny L says:

    Hi Debra, I am still getting the blank Combobox issue even after creating an intermediate name.

    Is there anything else that you can think of that may be causing this?

    I am using Excel 2013.

    Kind regards
    Ben

  5. Falk says:

    thanks made – solved my problem with ListFillRange at all

  6. Karin says:

    I’m having this problem and the workaround does not work either. It’s crazy because a few months ago prior to my company upgrading to 2013 I went through and changed all my OFFSET ranges to use table names as it calculates faster.

  7. Karin says:

    Edit, the work around is working – it really doesn’t make any sense though. I don’t want to go back to OFFSET because it makes everything so slow.

  8. Karin says:

    Do you know if this is fixed in 2016?

  9. Mete Tolay says:

    Dear Debra, hi,
    Thank you very much for both the solution and the statistics you gave. That graph makes the person feel that she/he is not alone who lives that problem.:)))
    Despite I have Excel 2010 and haven’t updated, I had the same problem. Your solution has worked for me.
    This problem is stupid, but your solution is genious!!!
    Thank you very much again, best regards…

  10. JBento says:

    Tanks for the the solution.

    Tip for ValidationLists with empty/blank rows in the middle, taken from a table or Range (follow the example):

    NameList refers to =Table[Names] (As example, this is the Range B1:B1000).

    NameListX refers to =OFFSET(‘Sheet1’!$B$2,0,0,MATCH(“*”,NameList,-1),1)

    Use =NameListX on your Data Validation. You created a named range based on the table column and, at the same time, you have clear from the list all the empty blank/Empty/Null rows.

Leave a Reply to Benny L Cancel reply

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