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

Thread: VBA Code to set up an audit trail in a separate sheet of a workbook.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,955
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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 by JLGWhiz; Jul 17th, 2019 at 01:44 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,955
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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 by JLGWhiz; Jul 17th, 2019 at 02:12 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by JLGWhiz View Post
    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.

  5. #5
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,955
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,955
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    Forgot to add: Leave the 'Log' sheet unprotected when you make it xlSheetVeryHidden.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thank you very much for your help!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •