How to remove values from drop-down if value deleted from validation list

Question

One sheet in my workbook is a datasheet, and the other sheet has the values for data validation. When eliminating a value from a cell (in a datasheet), I'm having trouble (containing data validation). The problem is that even though I tried to remove the value from the validation list, it didn't appear to have been removed from the cell. (view screen grab) For instance, "the value didn't delete from a cell in the data sheet (cell indicated in the screenshot) if I wish to remove volunteer name from the validation list."

I created a VBA program to add several values to the same cell when they are separated by commas. I would welcome assistance in resolving this problem. Below is my VBA code:

    Option Explicit
    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
    Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 3 Or Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 Or 
    Target.Column = 7 Or Target.Column = 8 _
                       Or Target.Column = 9 Or Target.Column = 11 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 _
              & ", " & newVal
        End If
        
        End If
        End If
        End If
        End If

        exitHandler:
        Application.EnableEvents = True
        End Sub

You can find the worksheet from the link below: (show1 Sheet is the data sheet and Validation Fields contains the drop-down values)

Answer 1

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const SEP As String = ","
    Dim c As Range, NewValue As String, OldValue As String, arr, v, lst, removed As Boolean

    On Error GoTo Exitsub

    If Target.CountLarge > 1 Then Exit Sub '<< only handling single-cell changes

    Select Case Target.Column
        Case 3, 4, 5, 6, 7, 8, 9, 11
            Set c = Target
        Case Else: Exit Sub
    End Select
    
    If Len(c.Value) > 0 And Not c.Validation Is Nothing Then

        Application.EnableEvents = False
        NewValue = c.Value
        Application.Undo
        OldValue = c.Value

        If OldValue = "" Then
            c.Value = NewValue
        Else
            arr = Split(OldValue, SEP)
            'loop over previous list, removing newvalue if found
            For Each v In arr
                If Trim(CStr(v)) = NewValue Then
                    removed = True
                Else
                    lst = lst & IIf(lst = "", "", SEP) & v
                End If
            Next v
            'add the new value if we didn't just remove it
            If Not removed Then lst = lst & IIf(lst = "", "", SEP) & NewValue
            c.Value = lst
        End If
    End If    'has validation and non-empty
    
Exitsub:
    If Err.Number <> 0 Then MsgBox Err.Description
    Application.EnableEvents = True
End Sub