Prevent Invalid Entries in Excel UserForm
In an Excel UserForm, you can use combo boxes, to show users a drop down list of items. If all goes well, the users will select an item from the list, and life will be good.
However, the occasional user likes to get creative, and types an entry that isn’t in the drop down list. And that can cause problems!
To restrict those creative users, you can set the MatchRequired property for the combo box to True.
Add a Select Item
Changing that MatchRequired property can cause problems though. In this example, the Excel VBA code also tries to clear out the combo boxes, after adding an order to the parts database sheet.
Because an empty string isn’t a valid entry, an error message appears — “Invalid Property Value”.
To prevent that error from appearing, you can add “Select” as the first item in the drop down list, and reset the combo boxes to that item, instead of clearing them out.
Download the Sample Combo Box Workbook
To download the sample file, go to the Excel Templates page on my Contextures site. In the UserForms section, look for UF0026 – Prevent Invalid Entries in UserForm. The file is in Excel 2003 format, and zipped. Enable macros after opening the workbook.
Watch the Excel UserForm Combo Box Match Video
To see the steps for changing the MatchRequired setting, and editing the code, you can watch this short Excel video tutorial.