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.
_________________
This code is great…and exactly what I need! However, when I add the code to existing code, I start getting errors. (Runtime Error 400 Form already displayed. Cant show modally) To clarify, if I remove the samecell code, the other subroutines run fine. I am only a beginning used of VB, so any help is appreciated.
‘ This checks to see if any hazard effects and severities are in the
‘ worksheet. If not, it warns.
Private Sub Worksheet_Activate()
Dim tRange As Range
Application.ScreenUpdating = False
Dim epty As Boolean
Worksheets(“DataSheet”).Visible = True
Set tRange = Worksheets(“datasheet”).Range(“A602:c700”)
epty = True
For Each c In tRange
If c.Value “” Then epty = False
Next c
Worksheets(“DataSheet”).Visible = False
Application.ScreenUpdating = True
If epty = True Then vbresult = MsgBox(“You do not have any hazard effects and severities loaded in this template. It is important that all people working on project risk documents have the same hazard effects and severities list loaded. See team QE to coordinate.”, vbOKOnly, “Info”)
End Sub
‘ This subroutine watches the activity on the sheet and determines what
‘ form to call depending on the cell that is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘ Show the form to select standardized hazards.
If ActiveCell.Row > 8 And ActiveCell.Column = 2 Then
Application.ScreenUpdating = False
SelectHazard.Show
Application.ScreenUpdating = True
End If
‘ Show the form to select the appropriate effect/severity pair.
If ActiveCell.Row > 8 And (ActiveCell.Column = 6 Or ActiveCell.Column = 7) Then
Application.ScreenUpdating = False
Hazard_Effect.Show
Application.ScreenUpdating = True
End If
‘ Show the occurrence dialog and load the appropriate hint
If ActiveCell.Row > 8 And (ActiveCell.Column = 8 Or ActiveCell.Column = 11) Then
Dim s As String
Application.ScreenUpdating = False
Load Occurrence
Occurrence.Show
Application.ScreenUpdating = True
End If
‘ Show the risk dialog box for pre-mitigation.
If ActiveCell.Row > 8 And ActiveCell.Column = 9 Then
Application.ScreenUpdating = False
Load Risk
Call Risk.hintSet(Cells(ActiveCell.Row, 7).Value, Cells(ActiveCell.Row, 8).Value)
Risk.Show
Application.ScreenUpdating = True
End If
‘ Show the risk dialog box for post-mitigation.
If ActiveCell.Row > 8 And ActiveCell.Column = 12 Then
Application.ScreenUpdating = False
Load Risk
Call Risk.hintSet(Cells(ActiveCell.Row, 7).Value, Cells(ActiveCell.Row, 11).Value)
Risk.Show
Application.ScreenUpdating = True
End If
‘ Show the selection of risk controls.
If ActiveCell.Row > 8 And ActiveCell.Column = 10 Then
Application.ScreenUpdating = False
RiskControls.Show
Application.ScreenUpdating = True
End If
End Sub
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
‘ This subroutine selects items from a validation list
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
Dim strSep As String
strSep = Chr(10) ‘line break separator
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 = 13 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 16 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 _
& “, ” & strSep & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
This is amazing, thank you!!!!!
Can we add check box in the drop down menu so that we know we have either selected or not selected this option. what code should we add for that
Quick question. Is there a way to create a new row every time a new item is picked and then delete that row when the item is removed?
Put another way, if I have the values of A, B, C in a picklist in cell A1, if I pick A, it puts A in cell B2, if I go back and pick B, it puts B in cell B2. However, I already have information in Row 2. What I would like for it to do it to create a new row and then place B in that new row. However, If I delete B, then that new row is deleted. Thoughts?
Hi,
this is working fine except if i protected the sheet.
Please help.
Thanks
I’m trying to use the SameCellAddRemove code in a new workbook. I am creating multiple data validation fields. I’ve set up the lists in a ‘Lists’ tab. I have added the SameCellAddRemove code to my tab that I want to use my lists. I can get the drop downs, but not the multiple values (or have them removed). I’ve set up my lists file to use Column 1,3,5, etc. I’ve tried to modify the code to include the Select Case statement you provided in a previous post. Nothing is happening. I just get to select the drop down box.
Here is the code, I’m hoping you can help me!!!!
Option Explicit
‘ Developed by Contextures Inc.
‘ http://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
Target.Value = newVal
Select Case Target.Column
Case 7, 14, 21
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
Case Else
‘do nothing
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub