+ Reply to Thread
Results 1 to 21 of 21

Multiple Worksheet Change Events

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,778

    Multiple Worksheet Change Events

    I've got code that I think should be working, but it's not giving the results I expect. I'm using Data Validation to provide lists for my users to select. What I want to do is: If the user selects cell E1 on the ActiveSheet, the value of E1 will be used to autofilter range T2 in Sheet("View2Pivot"). However, if the user selects cell E2 (a merged cell, E2-H2), then that value will be used to autofilter range U2 in Sheet("View2Pivot").

    What's happening is, when the user selects E1, nothing happens. When the user selects E2, nothing happens. Then when the user clicks on E1 again (doesn't change anything) the E1 change event triggers. But the E2 change event never happens. My code is below, and I'd sure appreciate some help in understanding this one.

    Thanks,
    John

    Please Login or Register  to view this content.
    Last edited by jomili; 12-13-2010 at 06:10 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple Worksheet Change Events

    Like so...though I'm not sure the effects of what would happen if you changed E1 and then E2 and then E1, etc, prior Autoflters should be removed before activating a new one elsewhere, so you'll have to play with that, or post a worksheet if you want us to help with that.
    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 12-04-2010 at 01:42 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,778

    Re: Multiple Worksheet Change Events

    Thanks JB. I tried your code; it's more elegant, but still doesn't get me there. I've posted a sample workbook so you can see what I'm facing.

    I hadn't thought about turning off the autofilter, but you're probably right about that. The way it would work for my users is that each one is responsible for one Division, so that would be the first level to look at. However, they would also want to be able to narrow down that view and look at specific Program Codes within that Division. That's what I'm aiming at. Thanks in advance for any help you can throw my way.

    John
    Attached Files Attached Files

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple Worksheet Change Events

    Correction:
    Please Login or Register  to view this content.

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Multiple Worksheet Change Events

    Try this
    Please Login or Register  to view this content.

    I'm not sure how the filters are meant to work.

    I have added a clear all cell to your sheet and removed "All" from the drop-down in E1

    Avoid merged cells

    Hope this helps
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  6. #6
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,778

    Re: Multiple Worksheet Change Events

    Marcol,

    I couldn't get to your example: for some reason our firewall sometimes blocks zipped files, and sometimes doesn't. Today's one of those days.

    i tried the code you posted, but didn't make any other changes. The code seems like it wants to work, but there's issues;

    What I want (way it should work): both autofilters/Change Events work in tandem. User selects his/her division, then the PAC they want within that division. They may look at 2-3 different PACs, one after the other, all within the same division, or they may want to compare a PAC in their division with one in someone else's. So, the two autofilter's need to work together.

    With your code, it seems I can choose Division, and PAC, but when I choose another PAC or division I get mixed results.

    Thanks,
    john

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,778

    Re: Multiple Worksheet Change Events

    Further clarification; the autofilters should always be active, and should always change according to what's currently in E1 and E2, so if EITHER change, only the affected Auftfilter should change. So, if I change E2, the U autofilter should change to the new value, the T autofilter should retain the last value it had (the current value of E1). If I should change E1, the T autofilter should change to the new value, the U autofilter should retain the last value it had.

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Multiple Worksheet Change Events

    Further clarification; the autofilters should always be active, and should always change according to what's currently in E1 and.......
    Okay that was probably what was throwing me.

    Can't look at it just now, I'll look back later and if your problem is not solved I'll give it another go.

  9. #9
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,778

    Re: Multiple Worksheet Change Events

    Thanks Marcol, I'm about to leave for the day, so if you think of a solution I'll be eager to see it when i come back. Thanks so much to you and JB for working with me on this. I really appreciate it.

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple Worksheet Change Events

    Here you go, for this to "appear" to be compatible no matter what cells are being changed, you actually need to reset both autofilters every time any change is made. Like so:

    Please Login or Register  to view this content.

  11. #11
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Multiple Worksheet Change Events

    I think there is more to this problem than what first meets the eye.

    I have made a few changes to this example and deleted your hidden sheets, they don't appear to affect the problem in hand and hence I don't have to zip the attachment.

    1/. I have added a lookup sheet that contains the unique Division numbers and unique groupings from Sheet "View2Pivot" Columns T:U.

    2/. The drop in "Chart-View 2" E1 is now validated by a Named range "Division"

    3/. When E1 changes the validation in E2 changes to suit. This validation is now a named list "Temp List" and is dynamic through the coding.
    Therefore you cannot select a Program Code Name (PAC?) that is not in the chosen Division.

    4/. Selecting E3 clears all filters and the values in E1:E2.

    I think you might have to consider VBa to control the chart display dependent on the filters chosen.

    This is a draught copy to see if what I am thinking is true, the code can be much improved if that is the case.
    Attached Files Attached Files
    Last edited by Marcol; 12-05-2010 at 10:18 AM. Reason: Typos

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Multiple Worksheet Change Events

    I implemented the code from this recent post into the workbook Marcol provided.
    Attached Files Attached Files
    Last edited by snb; 12-05-2010 at 11:51 AM.



  13. #13
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,778

    Re: Multiple Worksheet Change Events

    Okay, I got three attempts at a solution while I was away, and none of them are working as intended. Here are my results:

    JB, using your solution on both my example and my source sheet, the filter works on column I and H instead of T and U. Any ideas of what I'm doing wrong?

    Marcol,
    Your example appears to do an either/or instead of a both. For instance, if I filter for Division 513, I get all the PACs for 513. When I then also select FBI Disproportionaility Staff, I get that PAC for ALL Divisions, not just for 513.

    SNB,
    Your example looks cool; I like the Dependent Dropdowns. But choosing anything doesn't seem to affect the autofilters at all, no view changes.

    I appreciate all your help. I'll keep plugging away on my end to see if I can get any of your solutions to work, but I'd sure like to find a solution fast. My boss wants to present this today, and I hate that I'm stuck on what should be a simple thing. Any help you can give is very welcome.

    Thanks,
    John

  14. #14
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple Worksheet Change Events

    This should fix that:
    Please Login or Register  to view this content.

  15. #15
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,778

    Re: Multiple Worksheet Change Events

    JB,

    I'm getting a syntax error on these lines:

    Please Login or Register  to view this content.

  16. #16
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Multiple Worksheet Change Events

    Try this amended workbook, I think that is the filter problem fixed, sorry my mistake.
    Attached Files Attached Files
    Last edited by Marcol; 12-06-2010 at 11:28 AM. Reason: Forgot to save changes to workbook before saving

  17. #17
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple Worksheet Change Events

    Why are there underlines in your code? Take them out, they don't do anything. The code is underlined here in the forum to point out the changes.
    Please Login or Register  to view this content.

  18. #18
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,778

    Re: Multiple Worksheet Change Events

    JB, Sorry. I figured that out AFTER I'd tried the code. I'm working with it now, and so far it seems to be doing the trick.

    Marcol,
    Your latest example still has issues. I selected Division 510, which populated correctly with the Div 510 PACs. I then chose the "Brotherhood Initiative" PAC, which should have shown just that PAC for Div 510. Instead it changed to showing all Division and PACs.

  19. #19
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Multiple Worksheet Change Events

    Bad hair day. I forgot to save the changes before posting.

    I have changed the attachment in post #16 rather than post again, no point in leaving errors on the board.

    Have another look at the attachment there.

  20. #20
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple Worksheet Change Events

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  21. #21
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,778

    Re: Multiple Worksheet Change Events

    Sorry for taking so long to get back to you. I went with JB's code for now (easier to implement), but I like Marcol's dependent validation dropdowns, so may change in the future. Thanks so much for all your help on this issue.

    Merry Christmas!

    John

+ Reply to Thread

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