If you tried to make a comment on a blog post last week, you might wonder why it didn’t appear. I’m really sorry about that! I reported the problem on Thursday morning, and the support staff at the web hosting company couldn’t figure out how to fix it. Several days and phone calls later, I was lucky, and talked to the right person – she figured it out in 5 minutes, and we’re back in business.
I’ve extended the deadline for the giveaway for Jon Peltier’s awesome charting utility. Now you have until August 5th, at 12 noon Eastern time, so go to the giveaway blog post, read the rules, and add your comment. Good luck!
And now, back to our regular business…
Show a Drop Down List
You can make data entry easier by adding drop down lists on a worksheet. You can do this with a data validation list, but it has limitations. For example,
- can’t change the font size
- single column only
- no autocomplete
Use a Combo Box Instead
A combo box also lets you select from a drop down list, and you can change the font size, use autocomplete, and show multiple columns.
Add a Single Combo Box
For worksheets where you only need one or two drop downs, just add the combo box on the worksheet, and set its properties.
In the example shown above, the combo box is used to select a customer name, and there can be multiple locations for each customer. The list has 6 columns. The first column, ShipID, is hidden, and it shows the next 5 columns, so you can see the customer’s address information, to make sure that you’re picking the right one.
Then, in the cells below, INDEX formulas pull the related details for the selected customer location.
Use Multiple Combo Boxes
Too many combo boxes can cause problems in an Excel file, so it’s best to limit the number that you add.
If you need lots of drop downs on the worksheet, you can use my sample that has a hidden combo box. It pops up when you click, or double-click, on a data validation cell. This technique uses programming, but there is only one combo box, so it doesn’t add much to the file size.
Download the Address Combo Box File
To see how the customer location combo box works, you can download the sample file from my website. On the Sample Files page, go to the Functions section, and download the file FN0004 - ComboBox Selection. The zipped file is in xlsm format, and contains macros that update the ListFillRange.