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.
_________________
Grace, unfortunately, there’s no way to distinguish your correction from a selection in the drop down list. When you change the cell contents to “One”, that becomes the new value, and it’s added to the old value.
You could clear the cell, then select One from the drop down list.
Hi, I am able to apply this code to my worksheet just fine. However, when I select an item in error and I try to delete it from the cell, it actually adds another “wrong” item. The only thing I can think of is to clear the entire cell and start over with my selections. Is there any other way to work around this?
Thanks in advance
Hi Michelle, you can clear the cell and start over, or stop the event code from running while you make the change.
Dear Debra,
The examples are just great! Esp for those who don’t have programming skills like me (completely a layperson).
Currently am following the example – LineBreak. In my case, most of the pre-defined items are quire long (the longest has 249 characters so at least I have to set “110? width) in order to get a full display view from the drop-down box. I was wondering if there was a way to move the “selected items” cell down to next row instead of new column. i.e. Pick from C3 and the selected options to be displayed on C4?
Thanks
Hi Debra,
This is almost exactly what I have been attempting to accomplish. However, I need this to run on multiple columns, but not the entire workbook. Is there a simple way to do this?
For example; I want it to apply to all rows greater than 2 and all columns higher than C…
Thanks for the help!
weikee, you can change the code for the line break example, so the text goes into the cell below:
If Target.Value = “” Then GoTo exitHandler If Target.Offset(1, 0).Value = “” Then Target.Offset(1, 0).Value = Target.Value Else Target.Offset(1, 0).Value = _ Target.Offset(1, 0).Value _ & Chr(10) & Target.Value End If