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?

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

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.

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

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.

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

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!!!

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.