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 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.
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,
- On the Ribbon’s Formulas tab, click Name Manager
- Click the ProdList name, select the formula in the Refers To box, and press Ctrl+C to copy it
- Click the New button, and type a name for the intermediate formula -- ProdList_A
- Click in the Refers to box, and press Ctrl+V, to paste the formula, then click OK
- Next, click the ProdList name in the Name Manager list
- In the Refers To box, change its formula to: =ProdList_A
- Click the green check mark, at the left of the Refers To box, to save the change
- Click Close, to close the Name Manager
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.