VBA Clear Contents in Drop Down List
<p>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:</p>

<pre><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 &gt; 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 &gt; 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 &amp; ", ", "")
        End If
      Else
        Target.Value = oldVal &amp; ", " &amp; newVal
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub
</code></pre>

<p>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.</p>

<p>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?</p>

<p>Thanks for taking the time!  Have a great day!</p>