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.
_________________
hello,,
I have downloaded the workbook and using it before copying to my sheet and does not appear to be working on the file. The one I am interested in using is the fill down a column option. but i use vba with excellent. but it’s problem now..
you can help me please..
thankou..
tamer nagah elasid ali .,
+2 01004611058
Hi
I have used the following code but am trying to get it so that I can delete one thing in the cell rather than the whole record.
How do I do this?
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 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 _
& vbLf & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
i want to add up items from a drop down list in another worksheet but only if the item is selected from the drop down menu,for example: a1= 1S a2 = 2, i want a1 in the next sheet to say 1S and a2 to say 2, but if a1 = 2S then i want a1 in next worksheet to put a extra row in to accomidate the selection 2S
The file won’t download anymore…
@Amar, thanks, the link should be okay now.
Debra, I have used your SameCellAddRemove macro in a worksheet, to allow users to select/deselect multiple options from the drop down boxes. I would like for each selection to show up within the same cell, but on a different line. Can you please tell me how I would need to modify this code? In addition, I’d like for this code to only be applied to columns 4, 7 and 9 if possible.
Option Explicit
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 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 Sub