Forum Discussion
Solved
Reset two MUTUALLY dependent drop down lists in Excel while avoiding a fatal loop crash
I'm preparing an Excel report schedule that contains some filters so the user can restrict the scope they want to see, and two of those filters are YEAR and MONTH, as the couple screen shots below: ...
The section of code that resets the month triggers the section that resets the year, and that triggers the section that resets the month, etc., causing an endless loop.
To prevent this, turn off event processing at the beginning of the code, and turn it on at the end:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Range("B3"), Target) Is Nothing Then Range("B4").ClearContents End If If Not Intersect(Range("B4"), Target) Is Nothing Then Range("B3").ClearContents End If Application.EnableEvents = True End Sub
The section of code that resets the month triggers the section that resets the year, and that triggers the section that resets the month, etc., causing an endless loop.
To prevent this, turn off event processing at the beginning of the code, and turn it on at the end:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("B3"), Target) Is Nothing Then
Range("B4").ClearContents
End If
If Not Intersect(Range("B4"), Target) Is Nothing Then
Range("B3").ClearContents
End If
Application.EnableEvents = True
End Sub- Thanks!!!
