Is it possible to modify VBA coding for Multiple selection drop down lists to achieve the following.

Question

I found some VBA code to create multiple selection drop down list in excel cell. The existing coding prevents a selection from being added twice. Works great but not quite what I need. I am using the cell to allocate engineers to a project. Unfortunately, this cell needs constantly updating depending on who is available. For example if Joe Bloggs is sick he will need replacing by another engineer. As it stands, the only way to do this is to delete the contents of the cell and start again. What I really need is if a name is clicked in the drop down list a second time, it is removed from the list. I'm a beginner in VBA so any help greatly appreciated.

This is the existing code:

"Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("J:K")) Is Nothing 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"

Answer 1

Like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OldValue As String
    Dim NewValue As String
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("J:K")) Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo ExitSub
    NewValue = Target.Value
    Application.Undo
    OldValue = Target.Value
    If OldValue = "" Then
        Target.Value = NewValue
    ElseIf InStr(1, vbLf & OldValue & vbLf, vbLf & NewValue & vbLf) = 0 Then
        Target.Value = OldValue & vbLf & NewValue
    Else
        NewValue = Replace(vbLf & OldValue & vbLf, vbLf & NewValue & vbLf, vbLf)
        If Left(NewValue, 1) = vbLf Then
            NewValue = Mid(NewValue, 2)
        End If
        If Right(NewValue, 1) = vbLf Then
            NewValue = Left(NewValue, Len(NewValue) - 1)
        End If
        Target.Value = NewValue
    End If
ExitSub:
    Application.EnableEvents = True
End Sub

Answer 2

Perfect! Just what I was looking for. Thank you so much.

Just need to digest the code though to understand exactly what is happening in each process.

Best Regards

Answer 3

Have a look into this file:
https://www.dropbox.com/s/784cs327ji72z1m/Data%20Validation%20List%20Multiple%20Select.xlsm?dl=1

Andreas.