VBA Code to create an audit trail in Excel 2010

[Confused_UK]

Hi all

I am looking for some VBA code to record any changes to a workbook in one worksheet called "ChangesLog".

Ideally I would like it to show the name of the individual making the change, the date and timestamp of the change, which cell in which worksheet has been changed, and the old and new values, but in a table.

I have found the code below which works very well, but would like it broken into individual cells. Does anyone have any ideas?

[COLOR=#000000][FONT=Verdana]Dim PreviousValue[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> PreviousValue Then
Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
Application.UserName & " changed cell " & Target.Address _
& " from " & PreviousValue & " to " & Target.Value & " at: " & Time & " on: " & Date
End If
End Sub[/FONT][/COLOR]
[COLOR=#000000][FONT=Verdana]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PreviousValue = Target.Value
End Sub[/FONT][/COLOR]

Thank you in advance

 

[dchaney]

WHat do you mean "individual Cells"? I ran the code above and it works great, I changed something in E14 and it came back as $E$14:$E$14... or do you just want E14?

 

[Confused_UK]

On the Audit Log, column A would be the UserName, column B the Target Address, etc, rather than one long string

 

[dchaney]

Ahhh, gottcha, here try this:

Dim PreviousValuePrivate Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long
Dim ws As Worksheet

Set ws = Sheets("log")

i = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

If Target.Value <> PreviousValue Then
    With ws
        .Range("A" & i).Value = Application.UserName
        .Range("B" & i).Value = Target.Address
        .Range("C" & i).Value = PreviousValue
        .Range("D" & i).Value = Target.Value
        .Range("E" & i).Value = Format(Now(), "dd/mm/yyyy, hh:mm:ss")
    End With
End If

End Sub

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

 

[Confused_UK]

That's fantastic thank you. If Target.Address is the cell, what would be the reference for the worksheet?

 

[dchaney]

One way is to add this infront of Target.Address

ws.Name & " | " &

 

[Confused_UK]

ws.Name gives the Audit worksheet name, in this case "log", rather than the worksheet of the changed cell.

 

[dchaney]

Oh... yea sorry forgot I had the ws linked to log... try this

ActiveSheet.Name

Instead of

ws.Name

 

[Confused_UK]

Perfect!!! Thank you very much indeed. You are a star!!

 

[dchaney]

Glad I could help