Hey everyone!
I've just started picking up VBA a few days ago, and the first task I wanted to do was selecting multiple items from a drop down list and have them in a listed order.
Then came up an issue where I couldn't deselect a selected item, however I'd fixed that and now I've noticed whenever something is deselected, it'd keep creating that sort of blanks, as seen in the uploaded image.
I'd deselected the 3rd selection after selecting the last item and that's the result.
It could infinitely go on like that and I can't seem to find any solutions online regarding this to fit into my code and fix it.
Here's the code I've put together:
Truly appreciate any help in finding a fix for this!
I've just started picking up VBA a few days ago, and the first task I wanted to do was selecting multiple items from a drop down list and have them in a listed order.
Then came up an issue where I couldn't deselect a selected item, however I'd fixed that and now I've noticed whenever something is deselected, it'd keep creating that sort of blanks, as seen in the uploaded image.
I'd deselected the 3rd selection after selecting the last item and that's the result.
It could infinitely go on like that and I can't seem to find any solutions online regarding this to fit into my code and fix it.
Here's the code I've put together:
VBA Code:
Dim Rng As Range
Dim OldVal As String
Dim NewVal As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set Rng = Cells.SpecialCells(xlCellTypeAllValidation)
If Rng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, Rng) Is Nothing Then
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
If OldVal <> "" Then
If NewVal <> "" Then
If InStr(1, OldVal, NewVal & "") > 0 Then
OldVal = Replace(OldVal, NewVal & "", "")
Target.Value = OldVal
GoTo jumpOut
End If
If InStr(1, OldVal, "" & NewVal) > 0 Then
OldVal = Replace(OldVal, "" & NewVal, "")
Target.Value = OldVal
GoTo jumpOut
End If
If xValue1 = NewVal Then
xValue1 = ""
Target.Value = OldVal
GoTo jumpOut
End If
Target.Value = OldVal & vbNewLine & "" & NewVal
End If
jumpOut:
End If
End If
Application.EnableEvents = True
End Sub
Truly appreciate any help in finding a fix for this!