VBA Code to set up an audit trail in a separate sheet of a workbook.

jlc29369

New Member
Joined
Jul 17, 2019
Messages
7
Hello all,

I have the task of setting up an audit trail for all changes that are made to a spreadsheet.
Suppose the spreadsheet has Sheet 1 and a log sheet. I want the log sheet to record all changes made to Sheet 1 by displaying the user name, the change, the old value, the new value, the date, and the time.

Could someone please help me out with this?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
The first marco goes in the sheet code module for the sheet where the changes will occur (Sheet 1 per Post #1 ). The second code will go into a public numbered code module (e.g. Module1). The second code is to reset the event trigger if the first code should stop working due to a break in the code before the line that turns events back on. If you name you log sheet something besides "Log" then you will need to make that change in the code.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim ov As Variant, nv As Variant
nv = Target.Value
Application.Undo
ov = Target.Value
Target = nv
With Sheets("Log")
         On Error GoTo HDL:
        .Cells(Rows.Count, 1).End(xlUp)(2) = Environ("UserName")
        .Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = Target.Parent.Name
        .Cells(Rows.Count, 1).End(xlUp).Offset(, 2) = Target.Address
                If ov = "" Then
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 3) = "Blank"
                Else
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 3) = ov
                End If
                If nv = "" Then
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 4) = "Blank"
                Else
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 4) = nv
                End If
End With
HDL:
    If Err.Number > 0 Then
        MsgBox "The following error occurred:" & Err.Number & vbLf & Err.Description & "."
    End If
Application.EnableEvents = True
End Sub
Sub t()
Application.EnableEvents = True
End Sub
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
Forgot the date time stamp. This should cover it.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim ov As Variant, nv As Variant
nv = Target.Value
Application.Undo
ov = Target.Value
Target = nv
With Sheets("Log")
         On Error GoTo HDL:
        .Cells(Rows.Count, 1).End(xlUp)(2) = Environ("UserName")
        .Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = Target.Parent.Name
        .Cells(Rows.Count, 1).End(xlUp).Offset(, 2) = Target.Address
                If ov = "" Then
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 3) = "Blank"
                Else
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 3) = ov
                End If
                If nv = "" Then
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 4) = "Blank"
                Else
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 4) = nv
                End If
        .Cells(Rows.Count, 1).End(xlUp).Offset(, 5) = Now
        If .Columns(6).ColumnWidth < 15 Then .Columns(6).ColumnWidth = 15
End With
HDL:
    If Err.Number > 0 Then
        MsgBox "The following error occurred:" & Err.Number & vbLf & Err.Description & "."
    End If
Application.EnableEvents = True
End Sub
 
Last edited:

jlc29369

New Member
Joined
Jul 17, 2019
Messages
7
Forgot the date time stamp. This should cover it.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim ov As Variant, nv As Variant
nv = Target.Value
Application.Undo
ov = Target.Value
Target = nv
With Sheets("Log")
         On Error GoTo HDL:
        .Cells(Rows.Count, 1).End(xlUp)(2) = Environ("UserName")
        .Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = Target.Parent.Name
        .Cells(Rows.Count, 1).End(xlUp).Offset(, 2) = Target.Address
                If ov = "" Then
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 3) = "Blank"
                Else
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 3) = ov
                End If
                If nv = "" Then
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 4) = "Blank"
                Else
                     .Cells(Rows.Count, 1).End(xlUp).Offset(, 4) = nv
                End If
        .Cells(Rows.Count, 1).End(xlUp).Offset(, 5) = Now
        If .Columns(6).ColumnWidth < 15 Then .Columns(6).ColumnWidth = 15
End With
HDL:
    If Err.Number > 0 Then
        MsgBox "The following error occurred:" & Err.Number & vbLf & Err.Description & "."
    End If
Application.EnableEvents = True
End Sub
It works! Except for one thing I forgot to mention. Is it possible for this code to work if the "log" sheet is password protected, or is there some workaround for that? Obviously I don't want users to be able to manipulate the audit trails.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
You can do it with a protected sheet, but it is slow and clumsy. As an alternative, you can use the Very Hidden feature to hide the worksheet which can then only be viewed by using VBA to restore visibility. The Excel User Interface will not allow a user to unhide the sheet. The first macro below will hide the worksheet and the second one will allow you to make if visible for viewing. There is no time delay resulting from having to unprotect and re-protect the sheet as users make changes.

Code:
Sub secSht()
Sheets("Log").Visible = xlVeryHidden
End Sub


Sub vuSht()
Sheets("Log").Visible = True
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
Forgot to add: Leave the 'Log' sheet unprotected when you make it xlSheetVeryHidden.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,941
Messages
5,483,817
Members
407,415
Latest member
Anton1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top