Clean-Up Cascading Data Validation List Values

[Lisa C.]

I have two dependent data validation lists. The first list (in cell A1)
determines the values on the second list (in cell A2). When the value
selected in cell A1 is changed, the following code blanks out any value
already selected in cell A2. The code works when the value in cell A1 is
changed to a new value, but it doesn't work when the value in cell A1 is
deleted or blanked out. How can I fix this?

Option Explicit
Private Sub worksheet_change(ByVal target As Range)

If target.Address = "A1" Then
ActiveSheet.Range("A2").Value = ""
End If

End Sub

 

[Dave Peterson]

The code you posted won't run the "Then" portion of your code. The .address
will include the $ signs unless you do something different.

Option Explicit
Private Sub worksheet_change(ByVal target As Range)
If target.Address = "$A$1" Then
me.Range("A2").Value = ""
End If
End Sub

If this doesn't help, you may want to copy directly from the VBE and paste into
your followup message.

 

[Lisa C.]

This is my actual code. It runs the "Then" portion but only when I change
the value in $L$3 to another value. It doesn't work when I delete the value
in $L$3.

Option Explicit
Private Sub worksheet_change(ByVal target As Range)
If target.Address = "$L$3" Then
ActiveSheet.Range("D2135").Value = ""
End If
End Sub

 

[Jacob Skaria]

Dear Lisa

You must be doing the deletion after selecting a range which includes L3.
Please use the Application.Intersect as below

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Application.Intersect(Target, Range("$L$3")) Is Nothing Then
ActiveSheet.Range("D2135").Value = ""
End If
End Sub

If this post helps click Yes

 

[Dave Peterson]

Another variation:

Option Explicit
Private Sub worksheet_change(ByVal target As Range)
If intersect(target, me.range("L3")) is nothing then
'do nothing
else
me.Range("D2135").Value = ""
End If
End Sub

Using ActiveSheet may cause trouble if your code gets more complicated. The Me
keyword refers to the worksheet that owns the code (where you made the changes).