Selecting Multiples from Dropdown Menu Help

CA Guy

New Member
Joined
Oct 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I needed the option to select multiple items from a dropdown list in 2 different columns, and found code that works great (How to Make Multiple Selections in an Excel Drop-Down List –)

However, I also need the ability to delete selections later, but I keep getting an error saying the deletion doesn't match my list table items.

For example: if I select - Item 1, Item 2 Item 3, Item 4. Then at a later date, I might need to remove Item 3 from the list. Is there a way to delete Item 3 without having to clear the cell data and reentering only the valid Items?

My list for the dropdown menu is on a sperate worksheet (if that matters)

Here is the code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("E3:E163,H3:H163")) Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
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 & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi CA Guy,

could you next time post your code between code brackets? Just click on the little vba icon above the post area and paste your code. (see example below my post). A lot of people won't respond to posts without proper tagging.

Your explanation of the issue is somewhat unclear.
The code you show seems to have to do with the user entering something in either column E or column H. The code then checks to see if the new value did not appear in the old value. If so the two are combined. Else the old value is put back in the cell.
(Neat trick by the way, application.undo)

I see that the code adds two or more different selections in one cell. Now you want to remove one of the selections you made in the cell. And that is where the problem occurs.

This is because the validation is done before the changed cell value is passed to the worksheet_change event. And then Excel complains that the remaining selection does not match.

I agree this is strange if you had selected two and then deleted one of the two, because you would think the remaining one does match the validation list. I think this has to do with the vbNewLine you have inserted. With a delete this does not get removed completely it seems. Because when i replace it with a vbLf (LineFeed), I can remove the word plus linefeed without Excel complaining.

However!!! your code logic is wrong here: it will check to see if the value in the cell was the same as (part of) the previous content. If so it restores the previous content. So the multiple selection gets put back in place!

There are also some other mistakes in your code:
You start off with setting EnableEvents to True, where I think it should be false
Then you set on error to exit the sub but you don't restore on error to default. I am assuming you tried to do this in order to bypass the Excel validation error. In general it isn't good practice to use onerror other than some very specific cases. But you always need to set it back to default behaviour (using On Error Goto 0).

I was also missing an end if in your code.

As for coding efficiency:
Don't string your If Then Else all in one line. You will lose your way.
Use proper indentation. Your code should have looked like this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
   
    Application.EnableEvents = False
    On Error GoTo Exitsub       'Avoid On Error if not strictly necessary
    If Not Intersect(Target, Range("E3:E163,H3:H163")) Is Nothing Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
        Else
            If Target.Value = "" Then
                GoTo Exitsub
            Else
                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 & vbLf & Newvalue
                    Else:
                        Target.Value = Oldvalue
                    End If
                End If
            End If
        End If
    End If

Exitsub:
    On Error GoTo 0     ' linked to the on error above. Avoid On Error if not strictly necessary
    Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,122
Members
452,381
Latest member
Nova88

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