I have columns I want to be able to select several values from a dropdown. The issue is when I select 2 values from the dropdown and try to ‘manually backspace’ on the first value (to remove it) – it instead removes the second value, and keeps the first. If I have more than 2 values, and try to remove the first value, my sheet will throw a Stop Error (this – I am ok with – but if there’s a more elegant solution to accomplish my goals – please propose it).

E.g., Carrot, Eggs … If I remove Carrot, the list will then show Carrot … when in fact it should be Eggs. If it’s Carrot, Eggs, Celery and I remove Carrot … I will get Eggs, Celery (which is correct).

I use Data Validation/List/Stop Alerts on the columns with multiple dropdown selection capability. This is possible due to my creating ‘Names’ with ‘Define Name’ to use.

Another thing – any way to remove warning symbols from the multi selection dropdown columns when selecting multiple values? The warning symbol has a dropdown to select actions to take (Data validation error, Display Type Information, Help on This Error, etc.) – can I remove these warnings – or should I allow them? It doesn’t seem to disrupt the purpose of what I’m trying to accomplish?

My code:

Private Sub Worksheet_Change(ByVal Target As Range)
   Const SEPARATOR As String = "/ "
   Dim Oldvalue As String
   Dim Newvalue As String
   On Error GoTo Exitsub
   If Not Intersect(Target, Range("C:C, D:D, E:E, G:G")) Is Nothing Then
      If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
         If Target.Value <> "" Then
            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 & SEPARATOR & Newvalue
               Else
                  Dim parts() As String
                  parts = Split(Oldvalue, SEPARATOR)
                  Dim n As Long
                  For n = LBound(parts) To UBound(parts)
                     If parts(n) = Newvalue Then
                        parts(n) = "|#|"
                        Exit For
                     End If
                  Next n
                  Target.Value = Join(Filter(parts, "|#|", False), SEPARATOR)
               End If
            End If
         End If
      End If
   End If
Exitsub:
   Application.EnableEvents = True
End Sub