Worksheet_Change on Data Validation Value

Question

I'm using a Worksheet_Change to ClearContents on specific cells. The cell being evaluated for the change is "A10". This value comes from a data validation list. I've noticed that when there is a value in this cell and I click the dropdown, even if I re-select the same value from this list, the clear contents performs. Is there a way to avoid this? I'm worried that the users of this sheet will want to view the options in the dropdown, but will ultimately keep the original A10 value and still lose the contents of the other cells.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim rang As Range
  Set rang = Worksheets("Sheet1").Range("A10")
    If Not Intersect(Target, rang) Is Nothing Then
     Worksheets("Sheet1").Range("B10:B50000", "C10:C50000").ClearContents
   End If
End Sub

Thanks in advance!

Gary's Student Gary's Student · Accepted Answer · 2014-04-03T23:41:59

Here is a version of your macro that keeps track of the old value in A10

If no real change is made, it just goes away:

Dim OldValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rang As Range
    Set rang = Worksheets("Sheet1").Range("A10")
    If IsEmpty(OldValue) Then
        OldValue = rang.Value
        Exit Sub
    End If

    If Not Intersect(Target, rang) Is Nothing Then
        If OldValue = rang.Value Then
            Exit Sub
        End If
        OldValue = rang.Value
        Application.EnableEvents = False
            Worksheets("Sheet1").Range("B10:B50000", "C10:C50000").ClearContents
        Application.EnableEvents = True
    End If
End Sub

Because OldValue is DIM'ed above the sub, its value is preserved from call to call.

Worksheet_Change on Data Validation Value

1 Answers