Audit Trail Not Working 100% As Planned

Hello,

I'm new to VBA and I have some code for an audit trail I've found on the web that I'd like to make work for my project.

The goal is to have 5 columns of data, on another worksheet titled "Audit", as follows: Username of who made the change, Cell change location, Previous Data, New Data (overwritten or added new), Date/Time of Change.
What is currently happening is I seem to only be tracking changes that happen in column A of my primary worksheet. It does track Username, cell location, new data, and date/time of change. But it doesn't pick up what the previous data was and I am stumped on that part. I've tried changing certain lines to different ranges and I'm not seeing any changes in what it prints out.

Here is my code just for the Audit Trail portion:

'========================================
'AUDIT TRAIL
'========================================

Dim i As Long
Dim ws As Worksheet

Set ws = Sheets("Audit")
i = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
   If Target.Value <> Previous.Value Then
       With ws
            .Range("A" & i).Value = Application.UserName
          .Range("B" & i).Value = Target.Address
           .Range("C" & i).Value = Previous.Value
            .Range("D" & i).Value = Target.Value
           .Range("E" & i).Value = Format(Now(), "dd/mm/yyyy, hh:mm:ss")
    
      End With

    End If
 
PreviousValue = Target.Value

.
Create a sheet named "Tracker". Paste this code in the ThisWorkbook module :

Option Explicit

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim bBold As Boolean


If Target.Cells.Count > 1 Then Exit Sub
If ActiveSheet.Name = "Pricing" Then Exit Sub

'On Error Resume Next

    With Application
         .ScreenUpdating = False
         .EnableEvents = False

    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheets("Tracker")
            
                If .Range("A1") = vbNullString Then
                    .Range("A1:H1") = Array("Cell Changed", "Old Value", _
                        "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "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:= _
                     "OzGrid.com:" & 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
            
        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
On Error GoTo 0
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target
End Sub

Hi Logit,

I plugged in your code but it isn't working at all on my "Audit" sheet. I'm new to VBA so I may not be understanding everything that is going on in your code. For example, I looked up .HasFormula but I'm not sure its necessary to have in my sheet since I'm mostly dealing with data entry and not formulas (save a couple of cells).

Here is all of my code with yours plugged in at the bottom (attached). If you have time could you please explain what I'm doing wrong? I'd like to learn from my mistakes too so I can do this by myself in the future.

.
My apologies. After posting the code I realized some of it was "out of wack". I edited the code in my workbook but failed to
repost the correct code for your use. I am so sorry.

Here is the correct code :

Option Explicit

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim bBold As Boolean


If Target.Cells.Count > 1 Then Exit Sub
If ActiveSheet.Name = "Pricing" Then Exit Sub

'On Error Resume Next

    With Application
         .ScreenUpdating = False
         .EnableEvents = False

    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheets("Tracker")
            
                If .Range("A1") = vbNullString Then
                    .Range("A1:F1") = Array("Cell Changed", "Old Value", _
                        "New Value", "Time of Change", "Date of Change", "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:= _
                     "OzGrid.com:" & 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
            
        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
On Error GoTo 0
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target
End Sub

Thank you, Logit!

I actually made some changes too once I figured out how everything works. Thank you for your code, it works great!

Here is my version, the only difference is the order of the columns:

Option Explicit

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim bBold As Boolean


If Target.Cells.Count > 1 Then Exit Sub
If ActiveSheet.Name = "Audit" Then Exit Sub

'On Error Resume Next

    With Application
         .ScreenUpdating = False
         .EnableEvents = False

    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheets("Audit")
            
                If .Range("A1") = vbNullString Then
                    '.Range("A1:H1") = Array("Cell Changed", "Old Value", _
                        "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
                    .Range("A1:E1") = Array("Cell Changed", "Previous Value", "New Value", "User", "Date and Time of Change")
                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:= _
                     "OzGrid.com:" & 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
               
               .Offset(0, 3) = Application.UserName
               '.Offset(0, 4) = Date
               .Offset(0, 4) = Format(Now, "dd.mmm.yyyy hh:mm:ss")
            End With
            .Cells.Columns.AutoFit
            
        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
On Error GoTo 0
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target
End Sub

.
You are welcome.