Log Worksheet Changes

I have a requirement to log all the changes made to a particular worksheet. I cringe whenever I’m faced with that particular task. I could use a Worksheet_Change event coupled with a Worksheet_SelectionChange event to log the old cell value and the new cell value. I really hate that option for a couple of reasons. First, I would have code running all the time which would be a drag on the system. Also, it logs way too much stuff. For instance, if a user changes a cell value from 1 to 2, then changes it back from 2 to 1, you have two log entries that don’t add much to the log. It makes the log file messy and lessens its value in total. Another problem occurs when the user changes a whole slew of cells at once. Make a Worksheet_Change log procedure then delete the used range of the worksheet. With just a 10 column x 10 row worksheet, you’ve just made a 100 log entries with a noticeable lag in performance.

Speaking of changing a bunch of cells, I would have to save the old value in a module level variable using the SelectionChange event so that I could log it when the user fires the Change event. That wouldn’t be such a big deal if I could use the Target variable from each of those event procedures, but when the user selects a range of cells, I have to save the “old value” from every cell in the selection. What a pain.

Normally, I would recommend that people don’t log their changes. But I have situation that’s hard to defend. There’s a one worksheet workbook with a fairly limited UsedRange. It’s an important table that a lot of other sheets will use and that many users will need to update as necessary. Everything happens (or should happen) in this limited UsedRange and there will never be any other sheets in the workbook. It’s a prime candidate for change logging because many users can change the data and that data affects every other user. If things get screwy, there needs to be some accountability.

Nevertheless, I wasn’t going to use a Worksheet_Change event. There’s just too much to track and too many problems that can arise. So I settled on a different tack, which I (finally) describe here.

The basic structure is that I save a copy of the file when it’s opened and I compare the current file with this saved copy to see what’s changed. This has the benefit of keeping the log file fairly clean because the whole change/change back scenario isn’t logged. Only the final changes against the original are logged. It also has the advantage that the code is running less. With a Change event, the code runs whenever you do absolutely anything. With this, it only runs on Open, on Save, and on Close (as described in a bit).

I’m not saying there aren’t challenges with this because there are. At first, I decided that I would save a copy on Open and compare on Close. I’m sure the brighter of you have already figured out the problem with that. You can close a file without saving it. So now I have to determine if the user cancels the close, closes without saving, or closes and saves. That’s all well and good, but I only have a Before_Close event and these user decisions happen after that fires. Ultimately, I had to settle on logging at every save. If my goal was to only log real changes, this takes me farther away from that goal. Now a user can change a cell, save, change it back, and save it again. It’s still less than the alternative. Here’s the basic structure:

When the file is opened, I use the SaveCopyAs method to save it in its current state, heretofore referred to as OldCopy
When the file is saved, I compare the two workbooks and log the changes. Then I close the OldCopy, delete it with a Kill statement, and resave the NewCopy so OldCopy is now current.
When the file is closed, I kill the OldCopy. Not so fast my friend, as Lee Corso might say. I had to control the hell out of the BeforeClose event for this to work. The long and the short of it is that I had to eliminate the Cancel option and program my own “Save and Close”, “Don’t Save and Close” options. Either way, that file was closing. Not ideal, but that’s life.

Figuring out which cells changed posed another problem. Do I loop through the newer file and compare it to the older file or vice versa. Or do I compare all 65,000 rows x 256 columns to make sure I don’t miss something. I don’t think so. I ended up looping through the newer and comparing to the older, but I didn’t necessarily use the UsedRange of the newer. I counted the cells in the UsedRange from both sheets and used the Address property of the larger of the two to define the corpus of cells to change. This, however is not foolproof.

The main issue with differences in the UsedRange was deleting rows and columns. If the user deleted half the rows in the newer file, then the newer file’s UsedRange might be quite a bit smaller that the UsedRange of OldCopy. If I just loop through the UsedRange of NewCopy, then I’m not actually logging all the changes because I’m missing all the zeros in NewCopy that had values in OldCopy. Counting the cells gets close, but it’s not perfect. If I delete a bunch of columns in NewCopy but add a bunch of rows, the UsedRange may have a higher count, but still not catch every change.

Now that I type this, I can see that I should count the rows and columns separately and use the greater of each independent count to determine which range I should compare. Okay, I’ll change the code, but I don’t guarantee that I won’t screw it up.

I haven’t commented this code very well (count of comments = 0) so you’ll have to rely on the above description. I think the variables speak for themselves. Anything that starts with a ‘g’ is a global variable. Anything in all caps is a constant, proper case is a variable. The error handling scheme is straight out of Professional Excel Development. The variable gsIdentStandardsOpen holds the name of the person who opened. This global variable is set when the workbook is opened and a password is supplied. I’ll describe the password scheme in a future post. I don’t know if any of that helps, but there it is.

Public Function LogStandardsChanges() As Boolean
 
    Dim bReturn As Boolean
    Dim lFnum As Long
    Dim sOutput As String
    Dim wbOldStan As Workbook
    Dim wbNewStan As Workbook
    Dim rCell As Range
    Dim bChanges As Boolean
    Dim lMaxRow As Long
    Dim lMaxCol As Long
    Dim rRng As Range
   
    Const sSOURCE As String = “LogStandardsChanges()”
    On Error GoTo ErrorHandler
    Application.EnableCancelKey = xlErrorHandler
    Application.ScreenUpdating = False
    bReturn = True
   
    Set wbNewStan = Workbooks(gsSTAN)
    Set wbOldStan = Workbooks.Open(gsTemplateFldr & gsOLDSTAN)
   
    lMaxRow = Application.WorksheetFunction.Max(wbNewStan.Sheets(1).UsedRange.Rows.Count, _
        wbOldStan.Sheets(1).UsedRange.Rows.Count)
    lMaxCol = Application.WorksheetFunction.Max(wbNewStan.Sheets(1).UsedRange.Columns.Count, _
        wbOldStan.Sheets(1).UsedRange.Columns.Count)
       
    With wbNewStan.Sheets(1)
        Set rRng = .Range(.Cells(1, 1), .Cells(lMaxRow, lMaxCol))
    End With
   
    bChanges = False
   
    sOutput = String(60, “-“) & vbNewLine
    sOutput = sOutput & “Saved: “ & Format(Now, “yyyy-mm-dd hh:mm:ss”) & _
        vbTab & “By: “ & gsIdentStandardsOpen & vbNewLine
       
    For Each rCell In rRng.Cells
        If rCell.Value <> wbOldStan.Sheets(1).Range(rCell.Address).Value Then
            sOutput = sOutput & rCell.Address & vbTab & _
                “Old Value: “ & wbOldStan.Sheets(1).Range(rCell.Address).Value & vbTab & _
                “New Value: “ & rCell.Value & vbNewLine
            bChanges = True
        End If
    Next rCell
   
    sOutput = sOutput & String(60, “-“)
   
    If bChanges Then
        lFnum = FreeFile
       
        Open gsTemplateFldr & gsSTANLOG For Append As lFnum
       
        Print #lFnum, sOutput
       
        Close lFnum
    End If
   
ErrorExit:
    On Error Resume Next
    LogStandardsChanges = bReturn
    wbOldStan.Close False
    Kill gsTemplateFldr & gsOLDSTAN
    wbNewStan.SaveCopyAs gsTemplateFldr & gsOLDSTAN
    Application.ScreenUpdating = True
    Exit Function
 
ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msMODULE, sSOURCE) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If

End Function

So there it is. I’m tired of typing, but if there’s something I didn’t explain be sure to let me know. You comments on this strategy, as always, are welcome and expected.

Posted in Uncategorized

17 thoughts on “Log Worksheet Changes

  1. (I’ve never pasted code here, so we’ll see…)

    This is what I’ve used to get around the BeforeClose cancel issue. This code is called by the BeforeClose event. Then in the Deactivate event I check wb_closing – if it’s true, I do all the closing tasks.

    Dim wb_saved As Boolean
    Dim save_wb_or_cancel As VbMsgBoxResult

    ‘wb_closing is a module-level boolean

    wb_saved = wb.Saved
    If Not wb_saved Then
    wb.Saved = True
    save_wb_or_cancel = MsgBox(prompt:=”Do you want to save changes to ” & “””” & wb.Name & “?”””, _
    Buttons:=vbExclamation + vbYesNoCancel + vbDefaultButton1, Title:=”FlyBar”)
    Select Case save_wb_or_cancel
    Case vbYes
    wb.Save
    wb_closing = True
    Case vbNo
    wb_closing = True
    Case vbCancel
    wb.Saved = False
    Call toggle_fb_addin_installed_status(fb_addin_name, True)
    End Select
    Else
    wb_closing = True
    End If

  2. I’m sure there is a good reason why you didn’t just turn ‘track changes’ on. Never having used this I don’t know what that might be (although your post did cause me to start exploring it!).

  3. Couldn’t you just make a SaveCopyAs (appending the username and timestamp) every time a Save is made, and then just user good old human intervention to track down who made the offending change in the seemingly ocassional case that someone screws it up?

    I always need to remind myself not to spend so much time automating stuff that I rarely use. I must have spent four hours automating Outlook to look up ZIP+4’s from the US Postal Service website to save me the hassle of doing it manually when I enter new client contact information. I’ll probably need to use it 180 times before I’ve recouped my time investment, which will probably take about three years or more.

    -Greg

  4. Greg: That’s a pretty good solution. I could potentially have hundreds of archive files though. Some people save after every little thing they do. I’m only that way for a couple of days after I’ve lost some work.

  5. Hi Dick,
    I’m not sure whether I am correct. I had faced a same situation where I want to catch the real close event of work book. After trying many methods what I found as reliable method was hooking WM_DESTROY message for the workbook window. I had faced this situation when I was doing an add-in for catching excel events. Greg’s solution of combining the Deactivate event with the Before close event is excellent

    Thanks
    Xcelion

  6. I’ve bin through this idea (on an idea to save a full spreadsheet to a SQL database).

    There are some optimisations you could make:

    You only need to check on unprotected cells. Proteced cells won’t change(if sheets are proteced of course). Make shure you make named ranges of them. this way you can check selectively, and you are not dependent on actual cell addresses. You need to do this for all unprotected cells. Walking through all named ranges that are not protected will certainly be faster.

    You also need to discern between “tables” and fixed ranges. “Table” ranges are difficult because their cell amounts can grow and shrink. You’ll need to understand if they do this horizontally or vertically. You also need to understand if your table is postionally linked to other ranges/tables (with cell references), or linked through cell values (with lookups). The last situation means you have a key column(s) to match records with an older version of your table. In fact you are now creating more or less a multi value version database inside excel (like e.g. ORACLE has). To make this not too hard either save the whole table with each change, or maintain the table in a SQL database, and make it multiversion in there. Link to it in Excel and use that.

  7. I put in place a monitoring process on a worksheet which only allowed users to modify cell values:

    1. Create a very hidden copy of the worksheet
    2. For all cell’s to be monitored, add conditional formatting comparing its value with the value in the equivalent cell of the control worskheet.

    I find this works well as it only highlights values that differ from the original file, even if the value is changed many times.

  8. Just a word of warning…

    lMaxRow = Application.WorksheetFunction.Max(wbNewStan.Sheets(1).UsedRange.Rows.Count, _
    wbOldStan.Sheets(1).UsedRange.Rows.Count)
    lMaxCol = Application.WorksheetFunction.Max(wbNewStan.Sheets(1).UsedRange.Columns.Count, _
    wbOldStan.Sheets(1).UsedRange.Columns.Count)

    May give the incorrect results if the used range does not include A1. (Well, sometimes it could happen .)

    Dim wbNewStan As Workbook
    Dim wbOldStan As Workbook

    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim LastCol1 As Long
    Dim LastCol2 As Long
    Dim LastRow As Long
    Dim LastCol As Long

    ‘some nice assignments

    With wbNewStan.Worksheets(1).UsedRange
    LastCol1 = .Columns(.Columns.Count).Column
    LastRow1 = .Rows(.Rows.Count).Row
    End With

    With wbOldStan.Worksheets(1).UsedRange
    LastCol2 = .Columns(.Columns.Count).Column
    LastRow2 = .Rows(.Rows.Count).Row
    End With

    With Application
    LastRow = .Max(LastRow1, LastRow2)
    LastCol = .Max(LastCol1, LastCol2)
    End With

  9. Hi Dick,

    How do I call you’re code.

    I have tried to call “LogStandardChanges” from the excel AutoOpen macro, with no luck

    …Scott…

  10. Same question as Scott,

    I’m struggling

    Please HELP, I need a solution like this urgently

    THANX Kobus

  11. Please explain how did you disable the cancel option in title bar of the excel sheet.

    Thanks
    Seema


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.