title: Application-defined or object-defined error when any cell in worksheet is edited
body: <p>I am relatively new to VBA and have managed to edit a worksheet_change sub from example code found online (Thanks Tim Williams!) that allows multiple selection and deselection in drop down-lists within specified rows and at the same time clears dependent cell contents when parent drop-down lists are edited.</p>
  <p>I did have another version that worked a treat and included multiple separators &quot;,&quot;, &quot;and&quot; and &quot;to&quot; to construct short sentences/lists but didn't allow deselection. I've been struggling to combine an example code that includes deselection with other code that clears data in a single Private Sub. I have found many different methods but using some code posted by Tim Williams on another thread is the closest I've got to finding something that works. But I get an &quot;Application-defined or object-defined error&quot; pop-up in excel each time I try to edit a cell and I'm not sure why.</p>
  <p>The worksheet that it is associated with will be copied 10's or possibly even 100's of times in the same workbook and the workbooks themselves will be copied hence there are no worksheet or workbook references.</p>
  <p>I would be very grateful if someone could spot my error(s) and suggest how to fix them. Thanks. Here is the code.</p>
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
      
      Const SEP As String = &quot;, &quot;
      Dim c As Range, Newvalue As String, Oldvalue As String, arr, v, lst, removed As Boolean
      Application.EnableEvents = True
      On Error GoTo Exitsub
      
  
      If Target.Row = 14 And Target.Validation.Type = 3 Then
          Target.Offset(1, 0).ClearContents
          Target.Offset(2, 0).ClearContents
      End If
          If Target.Row = 20 And Target.Validation.Type = 3 Then
          Target.Offset(2, 0).ClearContents
      End If
  
      If Target.Row = 15 Or 16 Or 17 Or 21 Or 22 Or 28 Or 29 Or 31 Or 33 Then
      
      If Target.CountLarge &gt; 1 Then Exit Sub '&lt;&lt; only handling single-cell changes
  
      Select Case Target.Row
          Case 15, 16, 17, 21, 22, 28, 29, 31, 33
              Set c = Target
          Case Else: Exit Sub
          End Select
      
      If Len(c.Value) &gt; 0 And Not c.Validation Is Nothing Then
  
          Application.EnableEvents = False
          Newvalue = c.Value
          Application.Undo
          Oldvalue = c.Value
  
          If Oldvalue = &quot;&quot; Then
              c.Value = Newvalue
          Else
              arr = Split(Oldvalue, SEP)
              'loop over previous list, removing newvalue if found
              For Each v In arr
                  If Trim(CStr(v)) = Newvalue Then
                      removed = True
                  Else
                      lst = lst &amp; IIf(lst = &quot;&quot;, &quot;&quot;, SEP) &amp; v
                  End If
              Next v
              'add the new value if we didn't just remove it
              If Not removed Then lst = lst &amp; IIf(lst = &quot;&quot;, &quot;&quot;, SEP) &amp; Newvalue
              c.Value = lst
          End If
      End If
      End If    'has validation and non-empty
      Exitsub:
      If Err.Number &lt;&gt; 0 Then MsgBox Err.Description
      Application.EnableEvents = True
  End Sub
  </code></pre>