I have a piece of code which I am using which was written by Sumit Bansal which is working as I need it to
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$D$26" Or Target.Address = "$D$38" Or Target.Address = "$C$4" 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
However I have tried to amend the code to allow the user to remove a value if it has been selected in error, i.e. if they select
1
2
3
these appear in the cell on separate lines.
However 2 should not have been selected and needs to be removed so what appears in the cell is
1
3
and to appear on separate lines, not separated by a comma
The order of the selections should be the same as in the drop down list.
