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.
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.
(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
Mary Kissalot works for your company? I was wondering where she went. Tell her hi for me.
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!).
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
John: She told me to kiss you for her the next time I see you, but I think I’ll pass.
Andrew: You’d think so, but my reason is the same as yours – never used it. I don’t like that it shares the workbook.
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.
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
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.
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.
I like your site so much….
you are really genius..
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
Good one Dave. Thanks.
Hi Dick,
How do I call you’re code.
I have tried to call “LogStandardChanges” from the excel AutoOpen macro, with no luck
…Scott…
Same question as Scott,
I’m struggling
Please HELP, I need a solution like this urgently
THANX Kobus
Please explain how did you disable the cancel option in title bar of the excel sheet.
Thanks
Seema
Please let me know how did you eliminate close option in excel sheet title bar.
Thanks
Seema