Results 1 to 4 of 4

Worksheet Change event ignore change event

Threaded View

jomili Worksheet Change event ignore... 07-07-2011, 11:48 AM
StephenR Re: Worksheet Change event... 07-07-2011, 11:53 AM
jomili Re: Worksheet Change event... 07-07-2011, 11:59 AM
jomili Re: Worksheet Change event... 07-07-2011, 12:29 PM
  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,065

    Worksheet Change event ignore change event

    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
    Last edited by jomili; 07-07-2011 at 12:29 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1