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
Thanks so much for the new video. I did get mine working but have encountered a new problem. All the sheets in my file have password protection. Without protection the code works great. When I apply a password it do not function. Is that the way it is suppsoe to work or do I need to change something?
thanks
@Rick, make sure that the data validation cells are unlocked, before you protect the worksheet.
In the code, you can add a line at the top, to unprotect the sheet — include a password, if you used one:
Me.Unprotect Password:=”LOckEd”
In the ExitHandler, turn the protection back on:
Me.Protect Password:=”LOckEd”
Debra Dalgleish
May 1st, 2011 at 2:45 pm
@Rick, make sure that the data validation cells are unlocked, before you protect the worksheet.
In the code, you can add a line at the top, to unprotect the sheet — include a password, if you used one:
Me.Unprotect Password:=”LOckEd”
In the ExitHandler, turn the protection back on:
Me.Protect Password:=”LOckEd”
Debra
As regards the above I added the code as instructed (see below) but it still doesn’t work. The data validation cells are unlocked. If I use “LOckEd” as the sheet password it launches a pop-up window that asks for the password when I go to a cell (in the column defined in the code). If I use my normal password for the sheet I do not get a pop-up window but, the code does not work at all…..it just lets me enter one selection in the cell.
Am I still doing something wrong? In case I wasn’t clear before I must have the sheet password protected to prevent users from making changes to formula’s and other specific features…..for instances the list of entry selections for the cells the code is trying to control.
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect Password:=”LOckEd”
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 = 10 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:
Me.Protect Password:=”LOckEd”
Application.EnableEvents = True
End Sub
Hi, thank you for the tips above. Is there any way I can make a sum of each of these:
For example:
Line 2: red shirt, blue shirt, green shirt
Line 3: blue shirt, red shirt
Total: 2 red shirt
2 blue shirt
1 green shirt
Thanks!
Hi Debra , I need to make changes to my drop-down list(either to remove or add on),so how do I go about to do it?. Thanks.
i want to have dependent combo fill how can i get that
like in same example of i need on selection of month name i need list of days of that month
please do ghelp
thanks in advance