Results 1 to 3 of 3

How to Bypass SheetChange() Event Conditionally

Threaded View

  1. #1
    Registered User
    Join Date
    08-24-2010
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    45

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

    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
    Last edited by m3atball; 09-03-2013 at 01:33 PM. Reason: Mark as solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SheetChange Event
    By solidsnake5698 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2007, 02:01 PM
  2. sheetchange event macro.. what am I doing wrong?
    By Joshua.Buss@gmail.com in forum Excel General
    Replies: 7
    Last Post: 09-24-2005, 02:05 PM
  3. [SOLVED] SheetChange event restore old value
    By Anton Sommer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2005, 06:05 PM
  4. How to know what caused SheetChange event..
    By Srini in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-26-2005, 07:06 PM
  5. [SOLVED] Inserting Rows during a SheetChange event
    By Nirmal Singh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1