Track Changes using MS Excel VBA

By hunter ·
I am using the below to track changes to any cell within a worksheet:

Private Sub Workbook_Open()
Run "SaveWorkbookBackup"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.EnableEvents = False
UserName = Environ("USERNAME")

shn = ActiveSheet.Name

If shn = "TrackChanges_Record" Then


lr = Worksheets("TrackChanges_Record").Range("A65536").End(xlUp).Row + 1

Sheets("TrackChanges_Record").Cells(lr, 1).Value = Now
Sheets("TrackChanges_Record").Cells(lr, 2).Value = shn & "!" & Target.Address
Sheets("TrackChanges_Record").Cells(lr, 3).Value = Target.Value
Sheets("TrackChanges_Record").Cells(lr, 4).Value = UserName

End If

Application.EnableEvents = True
End Sub

The first part of the code just calls another macro which autosaves a .BAK copy of the worksheet into the same folder. The reason for this is (I assume) that the "SheetChange" code will only register when there is a change (and therefore not record the original value).

I would like to record not only the new value but the original. So I have a .BAK (temporary copy) of my worksheet... How can I call on the cell that's been changed in the .BAK copy and print that in the same "TrackChanges_Record" worksheet?

So what I am looking for in my track changes log is:

Date, Sheet/Cell, Original Text, New Text, User

Can anyone help?

Thanks in advance...

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Maybe not helpful but....

by robo_dev In reply to Track Changes using MS Ex ...

Prodiance is a commercial product to track changes in Excel spreadsheets:

Collapse -

Track Changes using MS Excel VBA

by hunter In reply to Maybe not helpful but....

Thanks for that. I'll certainly look into it. It looks like they have a whole heap of useful tools there. I did find a solution thanks to bomb #21 (TechGuy) and have started using Application.Undo and code now suits my needs. For those who are interested:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If ActiveSheet.Name = "TrackChanges_Record" Then Exit Sub

Application.EnableEvents = False

UserName = Environ("USERNAME")

NewVal = Target.Value


oldVal = Target.Value

lr = Sheets("TrackChanges_Record").Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("TrackChanges_Record").Range("A" & lr) = Now
Sheets("TrackChanges_Record").Range("B" & lr) = ActiveSheet.Name
Sheets("TrackChanges_Record").Range("C" & lr) = Target.Address
Sheets("TrackChanges_Record").Range("D" & lr) = oldVal
Sheets("TrackChanges_Record").Range("E" & lr) = NewVal
Sheets("TrackChanges_Record").Range("F" & lr) = UserName

Target = NewVal

Application.EnableEvents = True
End Sub

Thanks again... :-)

Related Discussions

Related Forums