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:
- Is there anyway of detecting the last-modified-info without using the SheetChange() event?
- 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!!!
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Cell A1 on each sheet will show the last modified timestamp
Application.EnableEvents = False
Dim isctrl As Boolean: isctrl = False
For i = 1 To Sh.CustomProperties.Count
If Sh.CustomProperties.Item(i).Name = "BDOSign_ControlSheet" Then
isctrl = True
Exit For
End If
Next i
If Not isctrl Then Sh.Range("A1") = Now()
Application.EnableEvents = True
End Sub
Bookmarks