Automate data audit trails in Excel VBA

Here’s a detailed explanation and VBA code to automate data audit trails in Excel. This code will help track changes made in a spreadsheet by recording information such as the user who made the change, the cell modified, the value before and after the change, and the date and time of the modification.

VBA Code to Automate Data Audit Trail

1. Create an Audit Sheet: First, create a new worksheet named « Audit » where all changes will be logged. The « Audit » sheet should contain the following columns:
   • Date & Time: to log when the change was made.
   • User: to log who made the change.
   • Modified Cell: to log the address of the modified cell.
   • Old Value: to log the value before the modification.
   • New Value: to log the value after the modification.

For example, the « Audit » sheet might look like this:

• Column A: Date & Time
• Column B: User
• Column C: Modified Cell
• Column D: Old Value
• Column E: New Value

Step 1: Writing the VBA Code

1. Open the VBA Editor (Press Alt + F11).
2. In the VBAProject pane, double-click the worksheet where you want to track changes (e.g., Sheet1).
3. Paste the following code into the sheet’s code window.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AuditSheet As Worksheet
    Dim LastRow As Long
    Dim OldValue As Variant
    Dim NewValue As Variant
    Dim UserName As String
    Dim CellAddress As String
    Dim CurrentTime As String
    ' Exit if multiple cells are modified (optional)
    If Target.Count > 1 Then Exit Sub   
    ' Set reference to the Audit sheet
    Set AuditSheet = ThisWorkbook.Sheets("Audit")
    ' Get the value before the modification (if available)
    OldValue = Target.Value
    ' Get the new value after modification
    NewValue = Target.Value
    ' Get the username of the person making the change (using computer's username)
    UserName = Application.UserName
    ' Get the address of the modified cell
    CellAddress = Target.Address
    ' Get the current date and time
    CurrentTime = Now()
    ' Find the last empty row in the Audit sheet
    LastRow = AuditSheet.Cells(AuditSheet.Rows.Count, "A").End(xlUp).Row + 1   
    ' Log the information into the Audit sheet
    With AuditSheet
        .Cells(LastRow, 1).Value = CurrentTime        ' Date & Time
        .Cells(LastRow, 2).Value = UserName          ' User
        .Cells(LastRow, 3).Value = CellAddress       ' Modified Cell
        .Cells(LastRow, 4).Value = OldValue          ' Old Value
        .Cells(LastRow, 5).Value = NewValue          ' New Value
    End With
End Sub

Detailed Explanation of the Code

1. Worksheet_Change Event:
   This event triggers whenever a change is made in the worksheet. The Target argument refers to the range (or cell) that was modified.
2. Setting up the Audit Sheet:
   The code defines a reference to the « Audit » sheet where the audit trail will be recorded. If you are using a different sheet for audit, change the sheet name accordingly:
   Set AuditSheet = ThisWorkbook.Sheets(« Audit »).
3. Checking for Multiple Cell Changes:
   If multiple cells are modified at once, the code exits to avoid logging unnecessary multiple entries. You can remove this condition if you want to log changes for multiple cells.
4. Capturing Information:
   • Old Value: The value before the change. This is captured from the cell before it is modified.
   • New Value: The value after the change.
   • User: The username of the person making the change, retrieved via Application.UserName, which is the current computer user.
   • Cell Address: The address of the modified cell, captured by Target.Address.
   • Date & Time: The date and time when the change was made, captured using Now().
5. Logging in the Audit Sheet:
   The information is logged into the « Audit » sheet starting from the first empty row. The code determines the next available row by finding the last used row in column A and then adding 1 to it.
6. Storing the Data:
   The following values are stored in the audit trail:
   • Column A: Date & Time of the change.
   • Column B: User who made the change.
   • Column C: Cell Address of the modified cell.
   • Column D: Old Value before the change.
   • Column E: New Value after the change.

Conclusion

This VBA code automates the creation of an audit trail for changes made in an Excel worksheet. The audit trail is saved in a separate sheet, allowing you to keep track of who modified what, when, and how. You can modify the code to fit your specific requirements, such as tracking only specific cells or including additional details in the audit. This is especially useful for environments where data integrity and traceability are critical, such as financial reports, project management, or regulatory compliance.