VBA to edit another sheet's private module?

[RightBrain]

I have a workbook containing 4 sheets. The sheets are used to record instances of various business events - one event per row. Rows may be added or existing rows may be changed.

Each sheet is coded via the Worksheet_Change event to place a timestamp in a column, indicating when a row has been added or changed.

The workbook will be distributed to 4 people, who will use it to record events all week. The 4 workbooks will be collected weekly, and new/changed records will be merged to form a newest version of the workbook, which will then be distributed back out to the users for updating during the following week.

Much of the merging process will be manual - I've made my peace with that. I've got a macro to sort the incoming sheets by timestamp, so I'll be able to tell which rows need to be copied. But I want to preserve the timestamps on the rows when they're copied into (what will become) the newest version. Since that newest version also has the Worksheet_Change module in all its sheets, when I do the copy the timestamps will be updated.

I've thought of building the newest version from scratch each time, and then just plopping the Worksheet_Change modules into it as a final step. But that's just so inelegant...

Is there a way for a VBA macro to either temporarily suspend all processing of Worksheet_Change code (or perhaps suspend all event-driven processing by Excel itself) or to write VBA that can reach into the private worksheet module and programmatically edit that module, effectively suspending the timestamping? Is there another way to address the problem?

Thanks.

[yay_excel]

Have you tried using

Please Login or Register  to view this content.

This turns off events until they are turned back on with

Please Login or Register  to view this content.

[yay_excel]

You can just open your master workbook, go into the VB editor, type Application.EnableEvents = False into the immediate bar and press ENTER, make your changes, and when you're finished type Application.EnableEvents = True into the immediate bar and press ENTER.

[RightBrain]

That does it. I assumed that because the worksheet_change modules all do the EnableEvents false and then true (to avoid the endless looping) that the EnableEvents=True there would negate the command previously entered via the Immediate window.

But I guess I should have tested that assumption before writing!

Thanks for your help.