Cascading Data Validation Drop Down Lists

[Lisa C.]

I currently have the following code tied to a worksheet. The target cell
(L3) and the dependent cells that I cleanup when the value of L3 is changed
are all on the same worksheet. I now need to modify the code because I need
to move the target field to a different worksheet but still have the
dependent cells stay on the current worksheet. The name of the worksheet is
'Enter Account' and the new target cell is cell H10. Also, do I leave the
code tied to the same current worksheet or should I move it to the new
worksheet with the target cell or to a module?

Option Explicit
Private Sub worksheet_change(ByVal target As Range)

If Intersect(target, Me.Range("$L$3")) Is Nothing Then
'do nothing
Else
'clean up cascading drop-downs if first value is changed
Me.Range("H18:W18").Value = ""
Me.Range("H19:W19").Value = ""
Me.Range("H20:W20").Value = ""
Me.Range("H21:W21").Value = ""
Me.Range("AL18:AL21").Value = ""
End If
End Sub

 

[Dave Peterson]

The code will move to under the "Enter Account" worksheet.

Option Explicit
Private Sub worksheet_change(ByVal target As Range)

If Intersect(Target, Me.Range("H10")) Is Nothing Then
'do nothing
Else
'clean up cascading drop-downs if first value is changed
with worksheets("Sheetnamethatgetscleanedupgoeshere")
application.enableevents = false
.Range("H18:W18").Value = ""
.Range("H19:W19").Value = ""
.Range("H20:W20").Value = ""
.Range("H21:W21").Value = ""
.Range("AL18:AL21").Value = ""
application.enableevents = true
end with
End If
End Sub

The "application.enableevents=false" would have been useful in your original
code, too. This tells excel to stop looking for changes and that way the
worksheet_change event would fire (over and over and over ...) with each change
made by the code.

 

[Lisa C.]

Here is my actual code but it is not working:

Option Explicit
Private Sub worksheet_change(ByVal target As Range)

If Intersect(target, Me.Range("$H$10")) Is Nothing Then
'do nothing
Else
'clean up cascading drop-downs if first value is changed
With Worksheets("Tailored Counter - Page 2")
Application.EnableEvents = False
.Range("H18:W18").Value = ""
.Range("H19:W19").Value = ""
.Range("H20:W20").Value = ""
.Range("H21:W21").Value = ""
.Range("AL18:AL21").Value = ""
Application.EnableEvents = True
End With
End If
End Sub

 

[Dave Peterson]

What module is the code in?

Is it in the worksheet module that contains the H10 that you're monitoring?