Audit Log Issues

[Medici]

Hello,

I'm currently using this code to keep a log of changes to a workbook.

Dim PreviousValue As VariantPrivate Sub Worksheet_Change(ByVal Target As Range)
Dim Errb As Integer
    On Error GoTo ErrTrap:
    If Target.Value <> PreviousValue Then
      With Sheets("log").Cells(65000, 1).End(xlUp)
        .Offset(1, 0).Value = Application.UserName
        .Offset(1, 1).Value = "changed cell"
        .Offset(1, 2).Value = Target.Address
        .Offset(1, 3).Value = "from"
        .Offset(1, 4).Value = PreviousValue
        .Offset(1, 5).Value = "to"
        .Offset(1, 6).Value = Target.Value
        .Offset(1, 7).Value = "On"
        .Offset(1, 8).Value = Now()
      End With
    End If
    Exit Sub
ErrTrap:
  ErrNum = Err
  
  If ErrNum = 13 Then
    '*** Multiple cells have been selected, treat them as one merged group*****
    Resume Next
  End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value

End Sub

However, it only tracks direct user input.

In my workbook. Cell P values change (via a Lookup function) whenever a user inputs a value in Cell A.

The current code doesn't track the changes in Cell P. It only tracks user changes in Cell A.

Is there a way to modify the existing code so that it tracks changes to Cell P too?

Any help would be appreciated.

I'm using Excel 2007

 

[Jeffrey Lopez]

Welcome to the Mr. Excel Message Board!

I copied your code (which is a handy snippet btw) and it seems to be tracking all changes across all cells. If you take off the "On Errors" can you see if you are getting any errors?

 

[Medici]

I took off the "On Errors" but no effect.

When a user inputs an ID in Cell A - Cell P has a lookup function that looks up that ID in another tab and populates the date in Cell P. If the user changes the ID in Cell A, then the lookup function in Cell P again looks up the ID and changes the date associated with that ID.

The code logs the changes in Cell A but doesn't record the changes from the previous date in Cell P with the new date.