How to Bypass SheetChange() Event Conditionally

Is there anyway to trigger the SheetChange() event sometimes but not all the time?

I've been asked to detect the last modified time stamp on every sheet in a normally 50-sheet workbook for review and QC purposes. Basically, reviewers can see the last modified info and a separate informational sign-off time stamp to make sure nothing is modified after the sign-off time. I used the SheetChange() event in ThisWorkbook but soon realized that it would disable the Undo/Redo feature because each time that event is triggered the Undo stack will be cleared. Not having the Undo/Redo will make our users very mad.

I have a couple questions:

1. Is there anyway of detecting the last-modified-info without using the SheetChange() event?
2. If I have to use SheetChange() for this, is there anyway to bypass it under certain conditions? I'm thinking, until there is a sign-off done on the worksheet, I really don't care when it was last modified. So at least until that time my users can have that Undo feature available. The challenge is, I can't figure out how to do an "Application.EnableEvents = False" outside the SheetChange() event so it won't be triggered all the time.

Here is the code I used for SheetChange(), it places the last modified value in cell A1 of each sheet. Thank you for your time and help!!!

Please Login or Register  to view this content.

Hey there,

Basically SheetChange() event never affects the Undo/Redo of Excel app. This event actually gets triggered when any cell value gets changed in any of the worksheets available within that workbook. It is actually getting flushed cause of the macro inside that event of yours - which includes looping and stuff. It pulls in the focus of the sheet and processor towards the loop - releasing the trace from the history and not saving the changes done by the macro - resulting in no undo/redo. If you use references without giving a focus to the sheet or processor, your historical activities can still be safe.

If your target is to get the details of the time stamps of each sheet then, you cannot use the recording towards the closure as you'll not come across the time it was updated.. so thus, can result in inaccurate time stamps. I've worked on a sample file per your requirements. Check and see if they satisfy your needs. What I have done is make one of the sheet to store the logs of each available workbook with even the username who modifies it.

Following are the code-behind for ThisWorkbook

Please Login or Register  to view this content.

Apart from this, I have created a module to keep track of Sheet names and providing flexibility to store the sheet names when newly added and if performed with any change records the time stamp. It refreshes the list as and when it finds that sheet name isn't available in the list. Go forth and try them if you'd like it.

Here's the code for that module. Also attaching the sampe workfile for your references.

Please Login or Register  to view this content.