Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VBA Code to create an audit trail in Excel 2010
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2015
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to create an audit trail in Excel 2010

    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?

    Code:
    Dim PreviousValue
    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
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
    End Sub
    Thank you in advance

  2. #2
    Board Regular dchaney's Avatar
    Join Date
    Jun 2008
    Location
    Ohio
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to create an audit trail in Excel 2010

    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?
    **ALWAYS test code on a copy of your workbook, NOT the original**
    **ALWAYS USE CODE TAGS (# above in the edit bar of your reply/post) **

    “Courage is being scared to death, but saddling up anyway.” John Wayne
    Running Office 2010 on Win 7

  3. #3
    Board Regular
    Join Date
    Dec 2015
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to create an audit trail in Excel 2010

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

  4. #4
    Board Regular dchaney's Avatar
    Join Date
    Jun 2008
    Location
    Ohio
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to create an audit trail in Excel 2010

    Ahhh, gottcha, here try this:

    Code:
    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
    **ALWAYS test code on a copy of your workbook, NOT the original**
    **ALWAYS USE CODE TAGS (# above in the edit bar of your reply/post) **

    “Courage is being scared to death, but saddling up anyway.” John Wayne
    Running Office 2010 on Win 7

  5. #5
    Board Regular
    Join Date
    Dec 2015
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to create an audit trail in Excel 2010

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

  6. #6
    Board Regular dchaney's Avatar
    Join Date
    Jun 2008
    Location
    Ohio
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to create an audit trail in Excel 2010

    One way is to add this infront of Target.Address

    Code:
    ws.Name & " | " &
    **ALWAYS test code on a copy of your workbook, NOT the original**
    **ALWAYS USE CODE TAGS (# above in the edit bar of your reply/post) **

    “Courage is being scared to death, but saddling up anyway.” John Wayne
    Running Office 2010 on Win 7

  7. #7
    Board Regular
    Join Date
    Dec 2015
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to create an audit trail in Excel 2010

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

  8. #8
    Board Regular dchaney's Avatar
    Join Date
    Jun 2008
    Location
    Ohio
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to create an audit trail in Excel 2010

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

    Code:
    ActiveSheet.Name
    Instead of

    Code:
    ws.Name
    **ALWAYS test code on a copy of your workbook, NOT the original**
    **ALWAYS USE CODE TAGS (# above in the edit bar of your reply/post) **

    “Courage is being scared to death, but saddling up anyway.” John Wayne
    Running Office 2010 on Win 7

  9. #9
    Board Regular
    Join Date
    Dec 2015
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to create an audit trail in Excel 2010

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

  10. #10
    Board Regular dchaney's Avatar
    Join Date
    Jun 2008
    Location
    Ohio
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to create an audit trail in Excel 2010

    Glad I could help
    **ALWAYS test code on a copy of your workbook, NOT the original**
    **ALWAYS USE CODE TAGS (# above in the edit bar of your reply/post) **

    “Courage is being scared to death, but saddling up anyway.” John Wayne
    Running Office 2010 on Win 7

Some videos you may like

User Tag List

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
  •