Dear Excel ppl,

I apologize in advance for possible mistakes with my english, it's not my native tongue.

I have the following problem:

I made an excel file, where I enabled via VBA-Code to pick multiple choices out of a dropdown list. So e.g. in Cell A1 theres a dropdown list I made with data verification, which shows Option A, Option B and Option C when you click on it. Via the VBA-Code you can click on Option A and Option C for example and then Cell A1 will have the entry "Option A, Option C".

Now, that works well and all. But, i cannot delete single options I've chosen out of the cell. Everytime I try to it just won't change. Using CTLR + Z won't work either. I tried to solve this by setting the option for when you enter something wrong from Stop to Information, but that didn't help either. So, everytime I choose a wrong option out the dropdown list, I have to delete the whole content of the cell and choose anew. I have a cell where I have over 20 options to choose from...When I missclick on the 7th choice, I have to restart. That's quite annoying...

Here is the code I used (Which btw I got from the internet...My VBA skills are basically Zero):

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2019/11/13
Dim xRng As Range
Set rngBereich = Range("D:T")
If Not Intersect(Target, rngBereich) Is Nothing Then
Dim xValue1 As String
Dim xValue2 As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" Then
If xValue2 <> "" Then
If xValue1 = xValue2 Or _
InStr(1, xValue1, "," & xValue2) Or _
InStr(1, xValue1, xValue2 & ",") Then
Target.Value = xValue1
Else
Target.Value = xValue1 & ", " & xValue2
End If
End If
End If
End If
Application.EnableEvents = True
End If
End Sub
Is there a way to solve this problem?

I thank you very much in advance for your help!


Have a nice day,

Jonny