Validate Entries in Excel Combo Box
One of my Excel sample files lets you double-click on a cell that has a data validation list, and a combo box pops up. The combo box has advantages, because you can set the font size, and the number of rows in the drop down list, and the entry autocompletes as you type.
However, one of the disadvantages is that the combo box lets you type anything, even if it’s not a valid entry from the data validation list. In the combo box shown below, I was able to type “Cold” as a month name.
And even though there are several cold months in Canada, there isn’t one that’s officially named “Cold”.
If I tried to enter “Cold” in the worksheet cell where the data validation list is, an error message would appear, and the entry wouldn’t be allowed.
Set the Combo Box Properties
In an ideal world, the combo box would prevent me from entering an invalid item. It has a Match Required property, and I have that set to True.
However, as the Excel Help so helpfully points out, “Not all containers enforce this property.” Unfortunately, the worksheet is one of those non-enforcing containers.
Change the Combo Box Style
Because the MatchRequired property doesn’t work, you can change the Combo Box Style property, to prevent people from typing non-list entries. We’ll select List style, which doesn’t allow freeform typing.
In my sample file, the combo box is only visible when you double-click on a data validation cell. To make a change to the combo box properties, follow these steps:
- Double-click on a data validation cell, to show the combo box.
- On the Developer tab, click the Design Mode command.
- Click on the combo box border, to select it, and click Properties, on the Developer tab.
- In the Properties window, scroll down to the Style property, and select 2- fmStyleDropDownList.
- Close the Properties window, and click the Design Mode command again, to turn that setting off.
Test the Combo Box
To test the revised combo box
- Double-click on a data validation cell.
- In the combo box, try to type an invalid entry, such as “Cold”
The entry doesn’t appear, because it’s not in the list. In the screen shot below, you can see the result when I tried typing Cold – it defaulted to January, the first item in the list.
Download the Sample File
To see the data validation combo box, and change its properties, you can download the sample file from my Contextures website – Data Validation Combo Box Named Ranges.