# Solved: Track Changes using MS Excel VBA



## DarkKman (Mar 16, 2009)

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

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...


----------



## bomb #21 (Jul 1, 2005)

Thinking about your last "challenge", there was some mention in _that_ code of an "Application.Undo" method.

Have you thought of utilising something like that?

(Notes: (i) I'd never heard of such a method before yesterday (ii) you'd presumably have to "flank" it with Application.EnableEvents = False/True)


----------



## bomb #21 (Jul 1, 2005)

Yup, definitely _something_ in this.

Start a brand new file. Insert and/or rename sheets until you have "Sheet1", "Sheet2" and "Changes".

In Sheet1!A1 enter A.

In Sheet2!A1 enter 1.

In Changes, enter these labels in A1:E1 -

Date/Sheet/Address/Old Value/New Value

, then paste this into the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Changes" Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
oldVal = Target.Value
lr = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Changes").Range("A" & lr) = Now
Sheets("Changes").Range("B" & lr) = ActiveSheet.Name
Sheets("Changes").Range("C" & lr) = Target.Address
Sheets("Changes").Range("D" & lr) = oldVal
Sheets("Changes").Range("E" & lr) = NewVal
Target = NewVal
Application.EnableEvents = True
End Sub

Change Sheet1!A1 to B, then Sheet2!A1 to 2. Then check Changes.

HTH


----------



## DarkKman (Mar 16, 2009)

Wow... Thank you SOOOoooo much bomb #21. This is EXACTLY what I was after. I'm very very new to VBA and finding all the weird and wonderful things that can be done and am learning a lot from this site.

I have just made one tweak to fit my purpose. I added the Username (so I can see who makes the changes). So code now:

```
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

Application.Undo

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
```
Again, thanks for all your help. I'm learning...


----------



## bomb #21 (Jul 1, 2005)

You're welcome. And thank _you_, discovering the existence of *Application.Undo* was most interesting.


----------

