Allow Manually Backspacing in Multi-selection Dropdown Cell - Currently Throwing Off Values

[PurplePlop]

I currently have the issue I cannot backspace on the first value in a cell without throwing all other values in the cell off. This seems to happen when the cell only has 2 values - a Stop Alert I’ve called ‘User Error’ (using Data Validation) is not thrown either. I’m trying to standardize things as much as possible.

How can I stop this from happening?

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

 

[Joe4]

Can you post some sample data and walk us through an example of what is happening and what should happen?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.