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,
Thank you for your help. I did not have macros enabled and when I did I was able to get it to work.
I appreciate your time and expertise.
Best regards,
Bill
I have a similar issue that others have posted, but I am not seeing an answer that fits what I am runnign into. I have worsheets with the VBA coding for Multiple Selections in a dropdown list. It works great. I have selected these cells to be “unlocked” when I protect my sheet. Once I protect my sheet, the ddropdown option still works, but I can no loner select multiple items…I can only select one per cell.
Any suggestions?
Good morning, Debra:
Thank you for the great code!
I have been playing around with your “SeparateRows” code.
Just a few questions:
1)I would like to insert the validation menu at cell M2 only. Also, I would like to have the items appears from M3, M4, M5 and onward. How should I achieve this?
2)In addition, how can I set a limit to the number of rows that the code will populate? For example, I only want users to be able to populate M3, M4, M5, M6, and M7. If the user wants to populate past M7, an error message will pop up.
Thank you for your help in advance.
Sincerely,
David
I had trouble with the locking of the sheet as well.
But here’s something that’s worked for me:
I’ve simply added the Me.Unprotect to both the top and bottom of the code.
I can lock and unlock my sheet and it works fine. I do not have a password of my sheet however.
Hope it helps some.
Option Explicit ' Developed by Contextures Inc. ' www.contextures.com Private Sub Worksheet_Change(ByVal Target As Range) Me.Unprotect 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 Select Case Target.Column Case 26, 92, 93 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 Target.Value = oldVal _ & ", " & newVal End If End If End Select End If exitHandler: Application.EnableEvents = True Me.Protect End SubTriplets,
The Me.Unprotect without a password works, but it promts for a password and you have to click okay without putting a password in. That would be fine for me, but I am sharing this spreadsheet with over 100 employees. It is not “fluid” enough to use that option. Do you know of other options that would allow part of the spreadsheet to be protected and the part where the I have my VBA code for my multi-item dropdown lists to be unprotected?
Any suggestions are apprecaited.
@Juli E – what version of Excel are you using? If you protect the sheet, with no password, you should be able to unprotect it without a password prompt appearing.
Also, if you unlock the data entry cells, before protecting the sheet, users should be able to select multiple items in those cells, even if the sheet is protected.
Debra,
I am using Miscrosoft Office Professional Plus 2010. I shut Excel down and started over. It appears to be working now on my end. I am going to send to other employees to test as well. Will it work the same if they do not have the same version as I do? Thanks, Debra!
Hi Debra. Thanks for the great code for the drop down lists. I think they are fantastic. I was wondering if you could help me modify the SameCellAddRemove code. This works exactly as I’d like except that it separates the items with a comma. I’d like the items to be separated with a line break. I looked at the coding and I’m not able to figure it out. My programming skills are limited to say the least. Any assistance you provide, would greatly be appreciated.
Matt