problems with audit trail

scotthannaford1973

Board Regular
Joined
Sep 27, 2017
Messages
105
Office Version
  1. 2010
Platform
  1. Windows
Hi all

I have the code below which should record changes to cells in a worksheet - recording cell, old value, new value, time, date and domain name of person that made changes. It works well, except:

1. the old value is never recorded and just shows a blank. how do I record the old value (maybe the value when the workbook was opened?)
2. the code only works when individual cells are updated, so if a range is updated, it's not recorded. Is it possible to record changes to an unnamed range of cells, or just individual cells?

thanks in advance


Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "SCHEMES" Then
        Dim bBold As Boolean
        Dim vOldVal As String
        If Target.Cells.Count > 1 Then Exit Sub
        'On Error Resume Next
        With Application
             .ScreenUpdating = False
             .EnableEvents = False
        End With
        If IsEmpty(vOldVal) Then vOldVal = "Broken"
        bBold = Target.HasFormula
            With Sheets("AUDIT TRAIL")
                '.Unprotect Password:="Secret"
                    If .Range("A1") = vbNullString Then
                        .Range("A1:e1") = Array("Sheet/Cell", "Old Value (not working)", "New Value", "Time", "Date", "User")
                    End If
                With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                    .Value = ActiveSheet.Name & " : " & Target.Address
                    .Offset(0, 1) = vOldVal
                    With .Offset(0, 2)
                        If bBold = True Then
                            .ClearComments
                            .AddComment.Text Text:="Scott Says:" & Chr(10) & "" & Chr(10) & "Bold values are the results of formulas"
                        End If
                        .Value = Target
                        .Font.Bold = bBold
                    End With
                    .Offset(0, 3) = Time
                    .Offset(0, 4) = Date
                    .Offset(0, 5) = Application.UserName
                End With
                .Cells.Columns.AutoFit
                '.Protect Password:="TrackerL2019"
            End With
        vOldVal = vbNullString
        With Application
             .ScreenUpdating = True
             .EnableEvents = True
        End With
        'On Error GoTo 0
    End If
End Sub
 
Last edited by a moderator:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Since you're monitoring every cell, the most practical option is probably to store the new value, use Application.Undo to restore the old value so that you can read it, and then put the new value back.
 
Upvote 0
1. the old value is never recorded and just shows a blank. how do I record the old value (maybe the value when the workbook was opened?)
2. the code only works when individual cells are updated, so if a range is updated, it's not recorded. Is it possible to record changes to an unnamed range of cells, or just individual cells?

I put the updated code.
1. To save the previous value, taking RoryA's suggestion.
2. I also added the part so you can modify several cells at once. But you must be cautious, I put it in the value 100, with a larger number maybe I can collapse the excel.

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Sh.Name = "SCHEMES" Then
    Dim bBold As Boolean, vOldVal As Variant
    Dim [COLOR=#008000]dato[/COLOR], [COLOR=#0000ff]c As Range[/COLOR]
    If Target.Cells.Count > [COLOR=#ff0000]100[/COLOR] Then Exit Sub
    On Error GoTo AppEnaEve
    With Application
      .ScreenUpdating = False
      .EnableEvents = False
[COLOR=#008000]      dato = Target.Value[/COLOR]
[COLOR=#008000]      .Undo[/COLOR]
    End With
    '
[COLOR=#0000ff]    For Each c In Target[/COLOR]
[COLOR=#008000]      vOldVal = c.Value[/COLOR]
      bBold = Target.HasFormula
      With Sheets("AUDIT TRAIL")
        '.Unprotect Password:="Secret"
        If .Range("A1") = vbNullString Then
          .Range("A1:e1") = Array("Sheet/Cell", "Old Value (not working)", "New Value", "Time", "Date", "User")
        End If
        With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
          .Value = ActiveSheet.Name & " : " & [COLOR=#0000ff]c.Address[/COLOR]
          .Offset(0, 1) = [COLOR=#008000]c.Value[/COLOR]
          With .Offset(0, 2)
            If bBold = True Then
              .ClearComments
              .AddComment.Text Text:="Scott Says:" & Chr(10) & "" & Chr(10) & "Bold values are the results of formulas"
            End If
            .Value = Target
            .Font.Bold = bBold
          End With
          .Offset(0, 3) = Time
          .Offset(0, 4) = Date
          .Offset(0, 5) = Application.UserName
        End With
        .Cells.Columns.AutoFit
        '.Protect Password:="TrackerL2019"
      End With
[COLOR=#008000]      c.Value = vOldVal[/COLOR]
    Next
[COLOR=#008000]    Target.Value = dato[/COLOR]
    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
  End If
AppEnaEve:
  Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top