There are a few versions of the data validation combo box technique on my website. The files use programming to make a combo box appear when you click, or double-click, on a cell that has a drop down list.
The advantage to using a combo box is that you can show the text in a larger font, and it autocompletes as you type, unlike a cell with a drop down list.
Add New Item to Drop Down List
Another one of my sample files let you add new items to a drop down list, on the fly. If you type a new item, a message box asks if you want to add it. Then, if you click Yes, it’s put into the list, in alphabetical order.
Add New Items to Combo Box
Someone recently asked if it was possible to combine those techniques, so I’ve created a new sample file that does just that! When you click on a cell that has a data validation list, a combo box appears. If you type an item that isn’t in the list, you’re prompted to add it.
NOTE: The Add New Item code is triggered when you press Tab or Enter to get out of the Combo box.
To see the file, and how it works, you can download it from my Contextures website. On the Excel Samples page, look for DV0062 – Data Validation Click Combobox – Add New Items. The file is in xls format, and contains macros.