VBA Code to create a list of changes from a workbook.

[nathandavies]

Hi All,

I was wondering if it is at all possible to create a list of cell changes using VBA code? my aim is to track cell changes changes in a worksheet and who has made them changes at what time? if possible i would like to be able to see before and after on the cells also?

eg

Cell - Before - After
A1 - 12-Mar - 15 Mar

Thanks In advance for any help on this.

ND

[MINCUS1308]

You could turn on tracking changes:
https://www.gcflearnfree.org/excel2016/track-changes-and-comments/1/

[MINCUS1308]

if that doesn't do it for you could use a Worksheet_Change & selection_change event to track the required changes - but you would need some kind of backing list.





'IN SHEET1'S WORKSHEET MODULE

Public MyInitialValue

Private Sub Worksheet_Change(ByVal Target As Range)
'IM USING SHEET2 AS A BACKING/TRACKING SHEET
i = 1
Do Until Sheet2.Cells(i, 3).Value = ""
i = i + 1
Loop

Sheet2.Cells(i, 1).Value = Target.Address
Sheet2.Cells(i, 2).Value = MyInitialValue
Sheet2.Cells(i, 3).Value = Target.Value
Sheet2.Cells(i, 4).Value = Date
Sheet2.Cells(i, 5).Value = Application.UserName
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyInitialValue = Target.Value
End Sub

[MINCUS1308]

Or you could just add a comment to the cell:

'IN THE SPECIFIC WORKSHEETS MODULE


Public MyInitialValue

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Cells(Target.Row, Target.Column).AddComment
ActiveSheet.Cells(Target.Row, Target.Column).Comment.Text Text:=Application.UserName & " made the following change on " & Date & "." & vbNewLine _
& "'" & MyInitialValue & "' => '" & Target.Value & "'"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyInitialValue = Target.Value
End Sub

[MINCUS1308]

if 'Date' isn't specific enough you could replace it 'Now'

[nathandavies]

Mincus1308, i'm going to try the comment one to start with. this seems to do what i require the only problem is the initial value is not working correctly.

the comment reads " => '15/03/2018'

It doesn't show the initial value, which will be a date.

[MINCUS1308]

so the down side to technique...
Its extremely error prone.

What ive asked excel to do is:
every time you select a new cell,
save the value.

and if you change the value in the cell
it will add a comment before excel shifts its focus to a new cell.

Right now that macro doesn't care about what address the saved value came from.

you could save the address when you save the value using a target.address statement
and then verify that address before adding the comment.

again this method is super error prone. using the built in tracking feature is the best method.

[nathandavies]

I would like to use the tracking mnethod but i have macro/code already in my workbook so it errors 99% of the time when i try to use the track changes.

[nathandavies]

Mincus1308, i have just found the following code on a different website whilst i was looking for something else. this adds the comment perfectly. Thought i would share in case you want to use it for something.


Private Sub Worksheet_Change(ByVal Target As Range) Const xRg As String = "A1:Z1000"
Dim strOld As String
Dim strNew As String
Dim strCmt As String
Dim xLen As Long
With Target(1)
If Intersect(.Cells, Range(xRg)) Is Nothing Then Exit Sub
strNew = .Text
Application.EnableEvents = False
Application.Undo
strOld = .Text
.Value = strNew
Application.EnableEvents = True
strCmt = "Edit: " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & _
Application.UserName & Chr(10) & "Previous Text :- " & strOld
If Target(1).Comment Is Nothing Then
.AddComment
Else
xLen = Len(.Comment.Shape.TextFrame.Characters.Text)
End If
With .Comment.Shape.TextFrame
.AutoSize = True
.Characters(Start:=xLen + 1).Insert IIf(xLen, vbLf, "") & strCmt
End With
End With
End Sub

The only problem i see with this type of code is it always adds a comment even when first inputting a value. it would be nice to put an edit button on the workbook so cells can only change when the button is pressed.