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.
_________________
Dear Debra and other Forum members,
I read through the original post and some of the comments. What I could not determine is whether it is possible to use this multiple choice lists with other dependent data validations. For example:
In my sheet I have 4 dependent data validations in columns A-D. Selecting a value in A, limits the choices in Column B, Selecting the values in Column B, limits the choices available in Column C, selecting the values in column C, limits the choices in column D.
However, selecting a choice in Column D, should provide a multiple choice list in column E, as more than one value may be applicable to the item that was selected in Column D.
Is this possible?
Is it possible to do this in more than one place in the same worksheet?
Your assistance would be greatly appreciated.
Regards,
Louisa
I purchased the Data Validation Multi-select premium in an attempt to create multiple drop downs on protected sheets. I used the instructions for the sample and instructions for the dependent lists workbook. I already had a sheet set up with lists on another workbook that is open and 16 single select and 4 multi-select. I tried to use your vba, just to reference on column with a validation (sample had 5 and I changed to 9) and that did not appear to work at all. Further, you guide states on page 32: “Click on any of the buttons, or the listbox, and you can see the selected control’s properties in the Properties window.” I cannot see “properties”
Any help would be appreciated. I desperately need multiple drop downs on a protected sheet (the affected cells would be unlocked, of course).
Thank you..
@Ed, thanks and I’ve sent you an email.
Hi Debra,
I have been using the coding succesfully for a spreadsheet at work. I am currently trying to analyse the data that I have accumulated and trying to count up the individual items in each multiple choice category. However, the CountIf function doesn’t seem to be working in Excel 2010.
I wonder if there is a remedy for this, given that the function is only designed to work with single items in a dropdown?
Christopher
Hi Christopher,
You can use the “*” wildcard with COUNTIF to find text within a string. There’s a sample on my website:
http://www.contextures.com/xlFunctions04.html#String
Watch for partial words though — it would find “pen” within “pencil”, as well as separately
Hi Debra,
Thank you for all of your hard work, it has been extremly helpful! If you do not mind, I have a followup question regarding a problem I have encountered with the SUMPRODUCT function.
On a different sheet, I am collecting the totals for the available variables in the multiple selection dropdown. I have used the SUMPRODUCT function because I am only calculating the totals when they are considered a “new” case. The formula works fine, until there are multiple selections in a cell. SUMPRODUCT can no longer recognise the variable if there is more than one variable in a cell. Is there a way to fix this? It would be a great help, seeing as I am trying to calculate totals. I hope I was able to adequately convey the issue!
Thanks in advance!
This is a great piece of code and this forum has answered all my issues so far! However I am having the following issue that had been posted twice and went unanswered! I would greatly appreciate help! Thank you!
Alex
December 27, 2010 at 7:59 pm · Reply
Hello Debra, I know the Same Cell example was simply an example, but I liked it alot and I added it to a worksheet as you know. I get a little problem that maybe you have made a fix for it.
The code works great, but when I click on the drop-down list and for some reason click another cell in accident, it duplicates what ever is in the cell at the moment.
Example: cell A1 currently has
apples, pears, oranges in the cell
for some reason, when I activate the cell A1 with the text, and then drag my mouse away and click some other cell, it generates duplicate data of whats inside cell A1. So I end up with: apples, pears, oranges, apples, pears, oranges.
I un-checked ‘ignore blanks’ but it still happens.
Any suggestions?
Thank you,
Alex
I cant believe I mixed this part on the original post! what an oversight! FIXED IT!
Hi Sarah … can you share how the duplication in the same cell was fixed? I’m having the same issue and not having much luck finding a solution. Thanks in advance!
Debra,
Is it possible that you can select a max number of items in the cell?
Example. max 3 dropdown items items in each cel?
Thank you,
Henk
Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
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 Target.Column = 11 Or Target.Column = 21 Or Target.Column = 31 Then
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
End If
exitHandler:
Application.EnableEvents = True
End Sub
Henk, you could check the number of commas in the OldValue, and if it already has 2 commas (separating 3 items), then don’t allow another selection