Help with a VBA Code (Dropdown list)

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).

Not working..

My mistake. Don't comment out the last line. Then run the Re-Enable_Events macro once.

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

Now it is working Weird..
But can you please the solution Alpha? Why did we comment out that particular line??

n.b: what do you recommend me to do to ameliorate myself in vba coding? Do suggest anybooks?

I don't know what caused your error in the first place. I couldn't replicate it. I don't know why it is not erring now. We didn't fix anything. All we did was disable the error checker so we might see what line caused the error should it happen again. If you do get an error now, you will have to ReEnabel_Events for the code to work again.

I don't have any specific recommendation for books. Check out the sticky thread at the top of this forum.

It is working perfectly actually. I can select multiple values from the dropdown list and write in any cell without the Error 9 message.

How do I ReEnabel_Events ?

run the Re-Enable_Events macro once.