+ Reply to Thread
Results 1 to 11 of 11

Multiple change events in one worksheet?

  1. #1
    Registered User
    Join Date
    04-21-2009
    Location
    Tulsa, Oklahoma
    MS-Off Ver
    Excel 2002
    Posts
    5

    Multiple change events in one worksheet?

    I am trying to have multiple change events work in one worksheet and can not get it to work at all.

    The simple setup is, I have a column for each month of the year.

    I have several rows of numbers beneath each monthly column.

    What I want to do is if I enter a date in say January (my change field), I want it to "ClearContents" out all of the data in the August column.

    This works fine currently.

    But say the next month I enter a date in February, I want to "ClearContents" in the September column. And so on for each month.

    My various attempts at this does not recognize the second change agent, so nothing occurs.

    Should I try to "call" each section, or use "Else If", or some other idea?

    Thanks in advance for any help available.

    I've tried attaching the worksheet in case that helps to see what I am attempting.

    There are extra "items" on the side and below the table that will be removed if I get this "change event" working. So this worksheet is a rough draft so far.
    Attached Files Attached Files
    Last edited by JBW; 04-22-2009 at 02:21 AM.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Multiple change events in one worksheet?

    Not sure I follow your example. Are you saying that whenever a cell in the range B4:M4 is changed you want the column for 6 months ahead to be cleared?

  3. #3
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Multiple change events in one worksheet?

    Hi

    I'm not sure exactly how large your range is in row 4, but try something like

    Please Login or Register  to view this content.
    HTH

    rylo

  4. #4
    Registered User
    Join Date
    04-21-2009
    Location
    Tulsa, Oklahoma
    MS-Off Ver
    Excel 2002
    Posts
    5

    Re: Multiple change events in one worksheet?

    Sorry for not being clear enough....

    The B4 through M4 fields are "dates".

    So this worksheet is an "inspection points deduction counter".

    Each month we get inspected in our store we get a certain number of points deducted if things are found wrong. We have to be concerned about points "monthly" and points on a 5 month rolling average.

    For example:

    If I've gone through the first 5 months of 2009, then in the 6th month I get inspected, what I want to do is put the "Date the store was inspected in cell 4 of the 6th month (in this example it would be G4).

    Then because I only need to keep the points accumulated for the past 4 months plus the current month, I need to clear the contents of the 5 month back, which in this case would be January so the contents of B10:B32.

    So:

    I enter the "STORE INSPECTION DATE" for June, that "change" would cause January's numbers to clear (not the January date in B4, just the detail in B10:B32).

    Then the same thing would happen every month after that, I input a date in July, the February numbers would be cleared, etc....

    Sorry if I'm explaining too much, I'm trying to be brief to not bore you but detailed enough to have it make sense.

    Thanks for the answers already given, I will look into that this evening.

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Multiple change events in one worksheet?

    Hi

    This seems to be the opposite of what you have detailed in your first post.

    How about you use your example file, and step through some examples. So you change the date in F4 and range??? gets cleared. If you put the date in I4 then range ??? gets cleared. Detail exactly why the range gets cleared.

    In your first post you seem to be clearing ahead, but in your second you seem to be clearing in arrears.

    rylo

  6. #6
    Registered User
    Join Date
    04-21-2009
    Location
    Tulsa, Oklahoma
    MS-Off Ver
    Excel 2002
    Posts
    5

    Re: Multiple change events in one worksheet?

    rylo.....

    Sorry this hasn't made sense.

    I'm going to try a different type of change agent and use one cell and use a 1-12 number input to know which "month" to clear.

    But I'll explain it here:

    My stores get inspected every month by the Subway inspectors. If things are wrong we have points deducted (such as someone not wearing their uniforrm, etc.). The points must stay under a certain level for a 5 month cumulative rolling total.

    So, let's say I am brand new and have never been inspected before.

    So January 1st, 2009 I get inspected, then Feb, then March, April, and May. Now when the June inspection occurs we do not need the numbers for January anymore. So when a June date is entered, I wanted to wipe out the data input during that month. The only reason for wiping it out is cosmetics since I can obviously always calc the current and last months totals for the 5 month total.

    So in basic terms, I want the current month data and the previous 4 months.

    Therefore:


    January 2010 would clear the data in August 2009
    Feb. 2010 clears Sept. 2009
    March 2010 clear Oct. 2009
    April 2010 clear Nov. 2009
    May 2010 clears Dec. 2009
    June 2010 clears Jan. 2010.
    etc.......

    It may be easier to designate one cell as the change agent and then clear the months data based on their input indicating the month of the inspection (i.e. 1-12)

    Did that make more sense?

    I'm trying to be brief to not make you waste your time reading. I apologize for it not being stated clearly.

    Thank you for your help.

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Multiple change events in one worksheet?

    Hi

    I've used your example file as the basis for this. If you put something into B4-F4, then nothing should happen as this is the earlies of the 5 months. But after that, you want to clear out the data for the month that is 6 months in arrears.

    Please Login or Register  to view this content.
    Using this code, if you put something into G4, it will wipe out the data from column B. If you put it into H4, it will wipe out column C.

    Is that what you want to do? I'd assume that you have a history going across the page, not just stopping in column N the way you have. If your example file doesn't really represent your structure, then put up a new file that is a real representation.

    rylo

  8. #8
    Registered User
    Join Date
    04-21-2009
    Location
    Tulsa, Oklahoma
    MS-Off Ver
    Excel 2002
    Posts
    5

    Re: Multiple change events in one worksheet?

    The file I attached is the actual file.

    I only want to have 12 columns, 1 for each month of the year.

    Since this is for a rollling 5 month calcuation total, I didn't want to keep adding months and have such a long spreadsheet.

    As you know, the code you provided works perfect going backwards.

    So June through December work great clearing the months January through July.

    Now my goal is to have January clear August, Feb clear Sept, March clear Oct, April clear Nov., and May clear December.

    So basically going forward if the fields changed are B4:F4.

    I'll try some things.

    I WOULD HAVE NEVER COME UP WITH THE CODE YOU PROVIDED.

    I don't write spreadsheets often enough although I wish I had need so I could learn all this great code you provided.

    Thank you rylo!!!

  9. #9
    Forum Contributor daksh1981's Avatar
    Join Date
    04-15-2009
    Location
    India
    MS-Off Ver
    2003 & 2007
    Posts
    110

    Lightbulb Re: Multiple change events in one worksheet?

    What I have found in your modules & its uses in your workbook is that you need to call a specific sub procedure in your change events. Like if you want to change August while January's data is changing then call the August's change event.

  10. #10
    Registered User
    Join Date
    04-21-2009
    Location
    Tulsa, Oklahoma
    MS-Off Ver
    Excel 2002
    Posts
    5

    Re: Multiple change events in one worksheet?

    I got it to work by adding rylo"s code from above and then rylo"s first code he gave me.

    Thanks for the other solution daksh1981. I tried to do calls early on but was doing something wrong. Fortunately it's working now with the code below and I don't have to bother you like I did rylo.

    It works great. Thank you so much for your help.

    Please Login or Register  to view this content.
    Last edited by JBW; 04-22-2009 at 02:10 AM.

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Multiple change events in one worksheet?

    JBW,

    Please edit your post to add code tags.
    Entia non sunt multiplicanda sine necessitate

+ 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