Worksheet Multiple Change Events

Hi,
I realize that it’s not possible to have on one sheet, multiple Worksheet Change Events, so
I tried to come up with an alternative method of handling it, but quickly realized that it’s beyond my capabilities at this time.

I would like to set up a worksheet change event on each of a number of identical sheets.
Each Change Event will run exactly the same macro.
The exception is the “DeptStr” string value that is relevant to the sheet calling the macro .

Each sheet will be a different department.

• If the Target in either range has data added, then data will be added to the cell offset one column to it’s right.
• If the Target in either range has data cleared, then data will be cleared in the cell offset one column to it’s right.
• There can only be one sheet active at a time.
• Only one sheet has data entered at a time.
• The two non-contiguous ranges are of equal length. Ex. (C3:C52) and (E3:E52) and are uniquely named in this case,
but they are not a necessary requirement in order to solve this problem if there is a more practical method employed.

=============================================================
This code, for all intents and purposes, is pseudo-code.
My hope is that someone may be able to create the actual code.

In this instance, myRngs are the range names for each sheet.
Would I have to Dim each range on each sheet and set it to “DeptName” and “Charges”?

===============================================
EACH WORKSHEET MODULE
==============================================

Please Login or Register  to view this content.

================================================================
MODULE 1
================================================================

Please Login or Register  to view this content.

I’ve attached a workbook with the sheets set up and examples of how the data should look.
Thanks for your time

Mark

max57
couldn't you use the workbook event

Please Login or Register  to view this content.

There is also a Workbook_SheetChange() event. You use it when you want the same Worksheet_Change() things happening on all worksheets:

Please Login or Register  to view this content.

Put that in the ThisWorkbook module and it will operate on all sheets equally.