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!
This is EXACTLY what I need to do for a project of mine. I have a column of dealers and a column of brands that these dealers carry. I want the brands column to be a list where I can select/deselect multiple from 13 different brands. So Joe carries brand x, Tom carries brand x-y-z, and Tim carries x-z.
Here’s the catch…
I’m on Excel 2011 for Mac! 🙂
I know VBA is present in this version but I have absolutely NO idea how to integrate your technique. Help!
Thanks!
Hi Debra!
Your code is sensational and it’s made such a difference to my spreadsheets. But I’ve got this one niggling error: I’ve created a spreadsheet for colleagues to enter call data. One of the colums has a drop down menu with 3 options – the options are drawn from a separate spreadsheet (‘List’) and I’ve used a named range (‘callback’) so that eachtime I refer to that drop down list, it’s the same spelling, etc, and I can add new options easily.
The problem is that whenever people type something different into that column and then try to edit it, or try to start a paragraph on a new line, it doubles what they’ve written.
For example: ‘yes.no.blah’, and then the person tries to edit it, or presses ALT+ENTER, then it becomes ‘yes.no.blahyes.no,blah’.
Here’s my code:
Option Explicit ' Developed by Contextures Inc. ' www.contextures.com Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Cells(Target.Row, 2) = Date End If Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim lUsed As Long 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 oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else lUsed = InStr(1, oldVal, newVal) If lUsed > 0 Then If Right(oldVal, Len(newVal)) = newVal Then Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2) Else Target.Value = Replace(oldVal, newVal & ", ", "") End If Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End SubThanks!
Hi Debra, thanks so much for your code. Previous to using your code I had used the “Excel Data Validation — Hide Previously Used Items in Dropdown” from the Contextures.com site and it was working fine. Then I added your code and your code works great but now the other code doesn’t work at all. I’d like to combine both yours and the other one so that I can select multiple items in the same cell and then those items are no longer available to select in subsequent cells.
Any ideas?
Hi Debra,
I’ve use your “same cell” code and now I need to sum the count in the multi items cells. I tried using sumif, countif, but it all seems to only count the 1st item and ignore the other selected items in the same cell. How can I do this?
Thanks.
Anna
Anna,
I am having the same problem you are. Have you had any luck in figuring out what to do with it only counting the first item?
Juli
Thanks so much, Debra. I’ve created a database and this is just what I wanted. Been searching the Net all around & found you. It didn’t work at first because I needed to close the file & open it again, (+ the macro thingy). Guys, Debra is gassss !!! <3
@Blanca, thanks! Glad you found this page, and got it working.
I am having an issue understanding the coding behind the same cell tab. I am tryng to create a drop down box with 6 selections. I created it using data validation and it works fine. Now I am trying to set up that drop down box so that my users can choose more then one selection and the results will be listed in the same cell separated by a comma. I copied the data valaidation down through several rows for I will need my user to make their selections for every applicable row. I looked at the sample sheet, the coding and other articles and still can’t see how to get this result. Can someone clarify for me what exactly I should be doing? I am currently using Excel 2007.