VBA Clear Contents in Drop Down List

Question

I've created a drop down list where every time you select something new from the dropdown it adds to what is already in the cell. Problem, is I'm trying to find a way to clear it, and I think I have my ordering wrong. Here's the code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Worksheets("Contact Log").Range("AE:AE,AI:AI,AM:AM,AQ:AQ,AU:AU,AY:AY,BC:BC,BG:BG,BK:BK,BO:BO,BS:BS,BW:BW,CA:CA,CE:CE,CI:CI")

On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal

  If oldVal = "" Then
    'do nothing
  Else
    If newVal = "" Then
      'do nothing
    Else
      lUsed = InStr(1, oldVal, newVal)
      If lUsed > 0 Then
        If newVal = "CLEAR" Then
          Selection.ClearContents
        ElseIf Right(oldVal, Len(newVal)) = newVal Then
          Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
        Else
           Target.Value = Replace(oldVal, newVal & ", ", "")
        End If
      Else
        Target.Value = oldVal & ", " & newVal
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

The problem I'm having is that sometimes, if I select Clear from the dropdown menu, it adds it to the list instead of clearing the contents of the cell. When this happens, selecting Clear again will successfully clear the cell contents.

Hopefully this makes sense, if you need me to I'll clarify. Is this issue happening because the ordering of my If statements is wrong?

Thanks for taking the time! Have a great day!

Answer 1

the first time you input "CLEAR", lUsed is 0 because you hadn't that string in the old value so you don't pass If lUsed > 0 Then check and thus don't reach the If newVal = "CLEAR" Then check

so you have to put ``If newVal = "CLEAR"check before theIf lUsed > 0 Then` one

as in this little refactoring of your code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String

    If Target.count > 1 Then Exit Sub

    Set rngDV = Intersect(UsedRange, Range("AE:AE,AI:AI,AM:AM,AQ:AQ,AU:AU,AY:AY,BC:BC,BG:BG,BK:BK,BO:BO,BS:BS,BW:BW,CA:CA,CE:CE,CI:CI"))

    If Intersect(Target, rngDV) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    On Error GoTo exitHandler

    newVal = Target.Value
    Select Case UCase(newVal)
        Case "CLEAR"
            Target.ClearContents

        Case vbNullString
            'do nothing

        Case Else
            Application.Undo
            oldVal = Target.Value
            If oldVal <> "" Then
                If InStr(1, oldVal, newVal) > 0 Then
                    If Right(oldVal, Len(newVal)) = newVal Then
                        Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
                    Else
                        Target.Value = Replace(oldVal, newVal & ", ", "")
                    End If
                Else
                    Target.Value = oldVal & ", " & newVal
                End If
            End If

        End Select

exitHandler:
    Application.EnableEvents = True
End Sub

where there's still a weak point in that every error possibly raising after On Error GoTo exitHandler statement would lead you to end the sub.

while maybe you want to handle the error risen by Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2) when the input chooses as the second value the same he chose as the first one

Answer 2

Clear the content before copy it in the cell :

    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
        If newVal = "CLEAR" Then
          Selection.ClearContents
          GoTo exitHandler
        end if
        .....