Audit Trail ... help.

[wisewood]

I have an important spreadsheet, which several people have access to and make changes to it.

I have come up with an idea, and would like to keep an AuditTrail so that if something goes horribly wrong with it, we have a record to look back and find out who did it, and when.

So far, i have a script which runs when the selection is changed, and takes a copy of the current row and pastes it onto a sheet called "AuditTrail".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Limit = Sheets("AuditTrail").Cells(Rows.Count, 1).End(xlUp).Row + 1
Rows(ActiveCell.Row).Copy Destination:=Sheets("AuditTrail").Rows(Limit)
End Sub

The problem with this is that if the user scrolls up and down or left and right, you end up with lots of un-necessary rows on the audit trail... i want it to only copy the row if the content of any of the cells is changed.

Also, i would like to paste the information onto AuditTrail, but not at column A, i would like it pasted at Column C, with Column A and B being the date & time, and the username of the person who made the change.

Can anyone help me out on this please?

 

[njimack]

Firstly, try using a Worksheet_Change or Worksheet_Calculate event - then the code will only run when a cell is changed or the sheet calculates.

To enter the additional info on the Audit Trail sheet, use the following:

With Sheets("AuditTrail")
.Cells(Limit,1) = Now
.Cells(Limit,2) = Environ("Username")
End With

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Limit1 As Long, Limit2 As Integer
Limit1 = Sheets("AuditTrail").Cells(Rows.Count, 1).End(xlUp).Row + 1
Limit2 = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
Range(Cells(Target.Row, 1), Cells(Target.Row, Limit2)).Copy Destination:=Sheets("AuditTrail").Range("C" & Limit1)
Sheets("AuditTrail").Range("A" & Limit1).Value = Environ("UserName")
Sheets("AuditTrail").Range("B" & Limit1).Value = Now
End Sub

 

[wisewood]

Perfect ... now ... how do i get the row & column (eg B132) number of the cell that has been changed also - i just realised that would make life SO much easier when trying to find where a change was made.

 

[VoG]

This adds the address in column C

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Limit1 As Long, Limit2 As Integer
Limit1 = Sheets("AuditTrail").Cells(Rows.Count, 1).End(xlUp).Row + 1
Limit2 = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
Range(Cells(Target.Row, 1), Cells(Target.Row, Limit2)).Copy Destination:=Sheets("AuditTrail").Range("D" & Limit1)
With Sheets("AuditTrail")
    .Range("A" & Limit1).Value = Environ("UserName")
    .Range("B" & Limit1).Value = Now
    .Range("C" & Limit1).Value = Target.Address(False, False)
End With
End Sub

 

[PraveshG81]

Hi,

I am not sure if I need to start a thread but I am using the code pasted above a little differently. In the code for Sheet1 I pasted:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Limit1 As Long
Limit1 = Sheets("DomAuditTrail").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("DomAuditTrail")
.Range("A" & Limit1).Value = Environ("UserName")
.Range("B" & Limit1).Value = Now
End With
End Sub

However what I would like to do is in cell A1 in the Sheet1 tab i want it to show "Last Updated on "m/dd/yyyy;h:mm" by "user". Is there anyway to add this to the above code? Lastly, is it possible for the DomAuditTrail to track only what happens below Row 3?

Thanks for your help!

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Limit1 As Long
If Target.Row < 3 Then Exit Sub
Limit1 = Sheets("DomAuditTrail").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("DomAuditTrail")
    .Range("A" & Limit1).Value = Environ("UserName")
    .Range("B" & Limit1).Value = Now
End With
Range("A1").Value = "Last updated on " & Format(Now, "m/dd/yyyy h:mm") & " by " & Environ("username")
End Sub