Hello Philip,
I too am trying to create a multi-select drop down using the data validation list
leveraging your example, I am unable to create the multiple selections - i have a new (enterprise) version of excel that is listed as 365
In the debugger, the "If Not Intersect..." is displaying OldVal as "" instead of the first choice in the drop down and when i try to add another it replaces not adds.
Separately, i had my drop downs on a separate tab but that was generating errors so i moved them back to the primary sheet.
Any help would be greatly appreciated
Thanks in advance,
Jamie
Hi Jamie,
Please supply your workbook, impossible to debug without it!
Phil
Unfortunately, I can not upload the spreadsheet due to privacy concerns
Any other ideas?
Private Sub Worksheet_Change(ByVal Target As Range)
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim OldVal As String
Dim NewVal As String
'If more than 1 cell is being changed
'If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, ActiveSheet.Range("DataC4")) Is Nothing Then
'Turn off events so our changes don't Trigger this event again
Application.EnableEvents = False
NewVal = Target.Value
'If there's nothing to undo this will cause an error
'On Error Resume Next
Application.Undo
'On Error GoTo 0
OldVal = Target.Value
If OldVal = "" Then
Target.Value = NewVal
Else
'Delete cell contents
If NewVal = "" Then
Target.Value = ""
Else
'This IF prevents the same value appearing in the cell multiple times
'If you are happy to have the same value multiple time then remove this if
'If InStr(Target.Value, NewVal) = 0 Then
Target.Value = OldVal & "," & NewVal
End If
End If
Application.EnableEvents = True
Else
Debug.Print "nothing"
Exit Sub
End If
End Sub
Hi Jamie,
That code as you've pasted it works if there is a named range called DataC4 on the ActiveSheet - see attached file.
The code will allow the same value from the DV list to be entered more than once because you've commented out the IF statement that prevented this from happening.
You state that "If Not Intersect..." is displaying OldVal as "" instead of the first choice in the drop down but the If Not Intersect line doesn't refer to OldVal so I don't understand what you mean
If Not Intersect(Target, ActiveSheet.Range("DataC4")) Is Nothing Then
Can you please explain the steps you go through to create the error with the code above and also tell me the actual error message (if there is one?).
Regards
Phil
How would I add multiple ranges in a spreadsheet for "DV_Range" and "DV_Range2"?
Hi Oscar,
Just change the IF statement in the Worksheet_Change event
If (Not Intersect(Target, ActiveSheet.Range("DV_Range")) Is Nothing) Or (Not Intersect(Target, ActiveSheet.Range("DV_Range2")) Is Nothing) Then
Regards
Phil
