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.
_________________
Hi Debra,
I have been trying to create a drop down list that allows user to choose from more than one option. Still cant get your code to work on my spreadsheet. This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
‘do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = AM Then
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
Target.Value = oldVal _
& “, ” & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
If you could plese help me.
Thanks
Not sure if you are still replying to this thread, but thank you so much for the time and effort into putting these together. I do have one question. Is it possible to modify the code to do two of these (SeparateRows & SameCellAddRemove) simultaneously for the same cell reference?
Hi Debra,
brilliant guide hopefully you will see this as I am at a loss.
I need to have multiple items in a cell (done) I then have a few columns after whose data is dependant on the previous (done)
How do I combine the two – when I try this my data validation in dependant columns only recognises my first choice. So if I pick Veg then fruit only veg options will show.
Thanks to anyone for help given.
Hi Debra, Just to start off you’re amazing. I’ve go a problem with images in comments and I was hoping for some help. I’ve been dabbling with creating a macro that allows a user to select an image from “my pictures” and then automatically inserting that image into a cell comment. Unfortunately, while the macro works great, we underestimated the popularity of the spreadsheet and now there are hundreds of pictures and the file has become too large. Is there a way to copy the image from the comment into a file structure and then replace the image in the comment with a link to the image’s new path. Not sure if this can be done but I need to do something to manage the size of the spreadhseet while still allowing images to be identified with the cell. By the way, your site is fantastic and has taught me a lot. Thanks!
Thanks Mike! Glad the info is helping you.
I don’t have an example that extracts pictures from comments, but there is sample code in this old newsgroup posting that might help you get started: Excel Comment Pictures
It’s for a single comment, and copies a picture of the comment.
You’d need to tweak it to go through all the comment cells, and remove the picture, then add a link as the comment text.
The link wouldn’t be clickable though, so it might be better to put the link in an adjacent cell.
Debra,
Is there a way to assign this code to specific cells rather then an entire column?
I have a macro that is assigned to a list at the top of column “C”, but need a macro similar to yours that will run in the cells/lists below it in column “C”.
Thanks,
John.
@John, you could change the code so it checks the target’s row and column:
If Target.Column > 3 And Target.Row > 2 Then
That is a great idea! Thank you SOOOO Much!
Regards,
John
I tried this and it is not working!