Hello,
I've got the following code (source Select Multiple Items from a Drop Down List in Excel and modified to affect my columns) which allows me to pick multiple drop downs in a cell but I'd like the option to unpick an item, at the minute if I pick 1 wrong drop down item I need to clear the cell and repick the correct ones only. Any suggestions?
As an example, if I pick "one", "two", and "four" but should have picked "three" instead of "four". I need to clear the cell and repick "one", "two", &"three"
Thanks in advance
Dave87
I've got the following code (source Select Multiple Items from a Drop Down List in Excel and modified to affect my columns) which allows me to pick multiple drop downs in a cell but I'd like the option to unpick an item, at the minute if I pick 1 wrong drop down item I need to clear the cell and repick the correct ones only. Any suggestions?
As an example, if I pick "one", "two", and "four" but should have picked "three" instead of "four". I need to clear the cell and repick "one", "two", &"three"
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 43 Or Target.Column = 44 Or Target.Column = 45 Or Target.Column = 46 Or Target.Column = 47 Or Target.Column = 48 Or Target.Column = 49 Or Target.Column = 50 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Thanks in advance
Dave87