VBA - Blank after deselecting a selected item from multiple selection drop down-list

Miriya

New Member
Joined
Oct 31, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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.

1635668450948.png


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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Line break after deselecting from multiple selection drop down-list
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,214,656
Messages
6,120,762
Members
448,991
Latest member
Hanakoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top