To help diagnose, comment out the error handler code (below). Then does the error message now have a debug button and if yes, what line is highlighted.
Note: you will have to re-enable Application.Events after the error.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As String
Dim newVal As String
Dim i As Long
Dim bRemoved As Boolean
Dim v As Variant
If Target.Count = 1 Then
If Not Intersect(Target, Columns(2).SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
Application.EnableEvents = False
' On Error GoTo ExitHandler
If Len(Target.Value) Then
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If oldVal <> "" Then
v = Split(oldVal, ", ")
oldVal = ""
For i = LBound(v) To UBound(v)
If v(i) <> newVal Then
oldVal = oldVal & IIf(Len(oldVal), ", ", "") & v(i)
Else
bRemoved = True
End If
Next i
Target.Value = oldVal & IIf(bRemoved, "", ", " & newVal)
Else
Target.Value = newVal
End If
End If
End If
End If
ExitHandler:
' If Err.Number <> 0 Then MsgBox "Error " & Err.Number & vbLf & Err.Description, _
' vbCritical, "ERROR: Worksheet_Change procedure": Err.Clear
' Application.EnableEvents = True
End Sub
Bookmarks