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 so much for this tutorial! I am using data validation drop down lists in many columns (i.e. yes/no) but I would like the Same Cell format code to be used for only columns 12 through 15. What change would I have to make to the code to make this possible? I know you answered an earlier question relating to this, but the question by Tinks57 was not regarding corresponding columns. Thanks again!
Debra – thank you so much for this tutorial! Excellent wealth of information here.
Using the “Separate Rows” example – is there a way to move the “selected items” cell down to the next row instead of a new column? i.e. Pick from G3 and the selected option to be displayed in G4?
Thanking you in advance – you are my “Shero!”
hi Debra,
I have the code working for my needs, but i’m trying to change the code so it only checks specific rows. Im using the Fill Down a Column code but would like to make it check only specific cells ie giving it a row and column check, but the code is different than in your examples, could to explain what i should add/change to get it to check a row and column in the fill down a column code?
Thanks
I’m having trouble making this code work for me. It is very unreliable. It will work one minute, but next time I open the worksheet – or try to use it in another workbook the exact same way, nothing happens. I’m saving everything and macros are enabled and they are in .xlsm format. I’m using Excel 2007 can this be why? Any ideas what I’m doing wrong?
Hi,
Thank you so much for this, it has really helped me.
Just a quick question, is there a way to stop duplicate selections. ie. if you have a list with “Rob,Bill,Ben,Steve” etc. and the user selects “Rob”, then “Steve”, and then tries to select “Rob” again, that it doesnt allow it as its already been selected?
Thanks so much
Debra,
No need to answer my question on October 25th – using the “Separate Rows” example. I figured out how to move the selected items directly underneath the data validation dropdowns (next available row), rather than the column to the right.
Thank you for the examples!