Worksheet Change event ignore change event

**jomili** · 07-07-2011, 11:48 AM

Okay, I know I've got a contradiction here; it's obvious I need help.

The situation: Cells E1:E3 are dropdowns for the Division, Program Area, and Account. The user picks his Division, whereupon my macro determines the available Program Areas. The user picks Program, whereupon my macro determines the available Accounts. User picks the Acct, my chart populates. All of this happens through autofilters and change events. What I want to happen is if there's a change in E1 (Div), I want to clear E2:J2 and E3:I3. But I DON'T want those "clearances" to show as change events, because I want to capture the subsequent action in E2 (Prog) and E3 (Acct).

The problem: Any change in E1 triggers a change in E2 and E3, so those macros run also I'd like to somehow run the macros only when my user makes a change, not when the automation clears the cells. In other words, I don't want Filter2 and 3 to run when I change E1, and I don't want the Filter 3 to run when I change E2. Any ideas how I can go about doing that?

My current code is posted below. Since this involves multiple sheets I didn't provide an example. If one is required for this please let me know.

Any help is greatly appreciated.

Thanks,
John

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

Application.ScreenUpdating = False

    For Each Cell In Target
           If Cell.Address = "$E$1" Then
           Range("E2:J2").ClearContents
           Range("E3:I3").ClearContents
           Run "Filter1A"
            
            Exit Sub
            ElseIf Cell.Address = "$E$2" Then
            Range("E3:I3").ClearContents
            Run "Filter2A"
            
            Exit Sub
            ElseIf Cell.Address = "$E$3" Then
            Run "Filter3A"
        End If
    Next Cell

Application.ScreenUpdating = True
End Sub

**StephenR** · 07-07-2011, 11:53 AM

If you put

application.enableevents=false
'code'
application.enableevents=true

the intervening code will not trigger the event.

**jomili** · 07-07-2011, 11:59 AM

Sweet! That should be an easy fix, and here I've been sweating it for hours. I'm implementing your solution as in the code below; let me know if you see anything obviously wrong. I'll let you know how it works out.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

Application.ScreenUpdating = False


    For Each Cell In Target
           If Cell.Address = "$E$1" Then
           Application.EnableEvents = False
           Range("E2:J2").ClearContents
           Range("E3:I3").ClearContents
           Application.EnableEvents = True
           Run "Filter1A"
            Exit Sub
            
            ElseIf Cell.Address = "$E$2" Then
            Application.EnableEvents = False
            Range("E3:I3").ClearContents
            Application.EnableEvents = True
            Run "Filter2A"
            Exit Sub
            
            ElseIf Cell.Address = "$E$3" Then
            Run "Filter3A"
        End If
    Next Cell

Application.ScreenUpdating = True
End Sub

**jomili** · 07-07-2011, 12:29 PM

I'm back! It seems to have worked beautifully! But now I have another problem, same project, but whole different flavor. But, I'll mark this one solved, and create another post. Thanks for quick and good help!