Reset two MUTUALLY dependent drop down lists in Excel while avoiding a fatal loop crash

[leolapa]

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:

I wrote a named formula into Data Validation so every time a user selects a specific year on B3, the MONTH drop down below will show only those months related to that particular year. Conversely, every time a user selects a specific month on B4, the YEAR drop down above will show only that year related to that particular month that was selected.

And then I wrote that basic VBA code below so that every time the YEAR drop down on B3 is erased, if there's anything already selected on the MONTH drop down on B4 it will get automatically reset as well, in order to avoid inconsistencies of what is selected between the two cells (i.e.: 2021 is selected in YEAR and Mar/2020 is already selected in MONTH, which would generate a report error).

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$3" Then

        Range("$B$4").Value = ""
    
    End If

End Sub

However, I also need the other way around to happen too: reset what's on the YEAR filter in B3 every time an user erases what's on the MONTH filter in B4.

I tried tweaking the above code to make that happen, but when I try to run it I either get a VBA code error or a fatal loop that ends up crashing and closing Excel.

Is it possible to achieve what I want? And if so, what would be the best coding route? I'm really a newbie with VBA, so.....

Thanks in advance

[#2 · Accepted solution]

@leolapa 

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

[#3]

Thanks!!!