I am using the below to track changes to any cell within a worksheet:
Code:
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
Else
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…
