+ Reply to Thread
Results 1 to 9 of 9

Multiple worksheet_change events on one sheet

  1. #1
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Multiple worksheet_change events on one sheet

    I need to have two different Worksheet_Change Events for one worksheet. I now know that there is a limit of one per sheet.

    I thought I would be slick and just set up a helper sheet to mirror the one I need to watch by pasting a link to the target range. Then setup a Worksheet_Change Event on the helper worksheet. But even though the helper sheet linked cells do change, the the Worksheet_Change Event does not trigger.

    Does anyone have any good workarounds for this?
    thanks
    Robert
    Last edited by Hammer_757; 03-05-2012 at 12:26 PM.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Multiple worksheet_change events on one sheet

    Hammer_757,

    Quote Originally Posted by Hammer_757 View Post
    I need to have two different Worksheet_Change Events for one worksheet. I now know that there is a limit of one per sheet.
    There is not a limit of one per sheet. You can have as many as you like. You just need to set up the worksheet_change event to watch multiple ranges. Here's an example of how to do so:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Multiple worksheet_change events on one sheet

    Why do you need to have two different change events?

    If we solve that problem I assume your other one becomes a moot point.

  4. #4
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Multiple worksheet_change events on one sheet

    @Tiger - Missing something? Some of these, perhaps ... [code]

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Multiple worksheet_change events on one sheet

    Quote Originally Posted by Andrew-R View Post
    @Tiger - Missing something? Some of these, perhaps ... [code]

    Shh.. I fixed it so fast there's not even an edited timestamp...

  6. #6
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Multiple worksheet_change events on one sheet

    Doesn't matter how fast you fixed it - I saw, and I'm telling the mods!

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Multiple worksheet_change events on one sheet

    Oh noes!
    /begs for mercy
    It won't happen again, mistuh, I promise!

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Multiple worksheet_change events on one sheet

    @ tigeravatar

    Appreciate the humor...don't appreciate the choice of words...reminds me of routines common back in the 50's and 60's.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  9. #9
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: Multiple worksheet_change events on one sheet

    Quote Originally Posted by tigeravatar View Post
    Hammer_757,
    There is not a limit of one per sheet. You can have as many as you like. You just need to set up the worksheet_change event to watch multiple ranges. Here's an example of how to do so:
    Thanks Tiger, as usual i was making it harder that necessary. It was almost drag and drop!

    Andrew, well, it's complicated :D. The sheet I’m working on is really a display or arrangement of filtered data that includes four quadrants of data around an image. It is a supervisor’s worksheet that he uses to list failed sonar transducers in quadrants around the job site (inside the sonar dome on a submarine) to help in job management. This project is combining two different person’s workbooks to eliminate double entry and cause both outputs to benefit from each other’s quality checks. If you want to jump to the last paragraph, you can skip the background information. (I got a little verbose)
    There is an existing analysis and planning workbook that has an Input sheet that that lists the 1100 transducers in the array and has fields (columns) for conditions (good/bad, is there interference, what component is bad etc) of each transducer signal path. There is an Analysis sheet that contains the failure criteria formulas and displays the entire array as a dental chart with failed locations (a very large display).
    Once a job order is received for a ship in an upcoming repair period, the planner fills out the input sheet and uses the Analysis sheet in the planning and estimation process. This data then goes through a pretty strenuous review chain. Once the review is complete the Job Supervisor creates his own workbook, reentering the data in an arrangement that suits him. The transducers are installed around a large sphere that they penetrate. During the repair there are people working inside and outside the sphere. Because of different physical restrictions in the sphere and in the sonar dome outside the sphere, he creates two different worksheets, one for each location.
    This has caused problems during the repair because of entry errors when creating the supervisors worksheets. Hence combining the workbooks to eliminate the double (and triple) entry.
    Now back to why I am using two change events on the Supervisors Worksheet:
    Depending on the location that the user selects, the quadrants contents will change and the image will change. Additionally, the user has a few filter choices to limit the data included. The user selects the Location and Display Filters via Data Validation cells on the worksheet. He then prints the sheet to take to the jobsite to assist in managing the repair job. The first event is the "location" cell change, firing the code that changes the image and changes a constant in the formulas that determine which failed items show up in the quadrants. The second event fires if any of the display filters are changed. When the location is changed, the Location code must run and then the Filter code runs. If a filter is changed, only the Filter code needs to run.
    The filter code modifies the cell format to indicate conditions; bold = Transducer is bad, underline = cable is bad, slashed fill = interferance, slash = rejected (not being worked this job), subscript = repair complete.
    Last edited by Hammer_757; 03-02-2012 at 04:46 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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