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/excel20...nd-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.

PrivateSubWorksheet_Change(ByValTarget AsRange)    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
EndSub

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.