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 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.
That name is added to the combo box’s ListFillRange property, so it shows the correct list for the selected cell.
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.
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.
Create a New Name
The lists were named ranges based on Excel tables. One was named ProdList, and here is its definition:
I thought the table might be causing the problem, so I created a new name, based on the first name:
- On the Ribbon’s Formulas tab, click Define Name
- In the New Name window, type a one-word name -- ProdListB
- Click in the Refers to box, and press the F3 key
- In the Paste Name window, click on the table-based name -- ProdList -- and click OK
- Click OK to complete the name
When I tested the new name in the combo box, it worked fine. Excel accepted the name without any problems.
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.