Help with a VBA Code (Dropdown list)

Hello,

The code in this example enable us to select multiple values from a dropdown list.

However , for example, if I want to delete Q8- from the list in the 4th row and select another one I get an error message.

Can anyone help with the VBA code?

Thank you

Try something like this...

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

Hello Alpha,

First off all thank you for replying to this thread. I tried the code you gave. I am still not able to delete a value from the list and add new one without deleting the whole list.

N.B: i removed the code i have and put yours.

How are you trying to delete the value?

If you re-select from the list a previously selected value, it is then removed. It seems to work for me in your example workbook.

hmm...I see your point. Your code is a solution actually.

What i wanted is to delete from a list a certain value and then selecting another one from the dropdown list.

Originally Posted by Naja

hmm...I see your point. Your code is a solution actually.

What i wanted is to delete from a list a certain value and then selecting another one from the dropdown list.

How did you want to delete? You would have to be more specific.

Otherwise, mark this as solved.

Hello,

I entered your code, I am able to delete a value from the dropdown list BUT if I try to type anything in any cell, i get this error message :
''Error 9 Subscript out of range''.

Can you fix it?

Thank you

Here is the file with the code you gave me Sample N with new code.xlsm

I cannot replicate the error with the file you provided. In several cells I typed, deleted, copied. Everything I tried produced no errors.

Does the error message have the title "ERROR: Worksheet_Change procedure"?

Does the error message have a Debug button? If yes, is a line of code highlighted when you click it?

Also, no need to PM me. I get notified when you reply to this thread.

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

I did that. Now i cannot choose multiple values from the dropdown list...

Here is the new file with the your new code

Sample N with new code3.xlsm

You only pasted part of the code to the new file.

All this is missing...

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

Done Sample N with new code4.xlsm

I still cannot choose mutliple values... and no error message

Note: you will have to re-enable Application.Events.

Put this code in a standard code module (e.g.; Module1) and run it.

Sub ReEnable_Events()
    Application.EnableEvents = True
End Sub

I am a little confused..
But i think i did that. Have a look Sample N with new code4.xlsm

I got nothing.

N.B: thank you so much for trying to figure this out with me

What if we try in the code make a direct reference to the activie sheet or something like that?

Originally Posted by Naja

What if we try in the code make a direct reference to the activie sheet or something like that?

What if we try to make a reference to the activite sheet?

Not needed and also not needed the 2nd time and for future reference it wont be needed.

Close Excel completely and restart it and open only the last version of the workbook (code4).

What if we try to make a reference to the activite sheet?