You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
Debra,
Thank you for your quick response. I will get to testing. By the way, I really like the teaching methods and examples employed here. Thanks again.
~gh
Hi
This is really useful and we have transferred the same logic to an excel database. However, I need to lock the excel spreadsheet to prevent some cells being changed (obviously not those using the multiple value validation).
What I have found is that the spreadsheet then has to be unlocked to allow this VBA to work correctly on the cells using the validation. When the worksheet is locked it works the way it normally would, current string of values is replaced with new value. Existing values are not retained.
Is there any way around this?
Thanks in advance for any help.
Kevin
Hi i want to give hh:mm – hh:mm format in excel cells how do i do that, please do the needful
Debra, think it is great what you’ve done. I’m a total novice in programming. I want to combine two of your examples, samecell and linebreak. Can you advise how to do this?
Hi, Great function. I’m having a problem with getting the “Select Case Target.Address” portion. I’m using 2007, and need to only change the range as I’m using headings. The code with the column selected works fine, but when I added the change as suggested to Terry, the cell only displays the last selection instead of accumulating them.
My problem is this:
I have a form: Item name weight cost location
I have a list of items and in the next block I have their weight in the next their cost and the next their location.
I want to select the name in the drop down list under item name, but I want the weight box, cost box and location box to automatically fill with the data cell information. How do I do that?