VBA audit trail - modifications

[Flashback]

Hello,

pleas, how I could exclude changes of blank value? I no need log, when emty cell is changed.

Thank you!

Dim PreviousValues As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Store current values of all cells
    Application.EnableEvents = True
    PreviousValues = Me.UsedRange.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim LR     As Long
    Dim c      As Range
    
    On Error Resume Next
    
    Application.EnableEvents = False
       If Target(1).Value <> PreviousValues(Target(1).row, Target(1).Column) Then
            With Sheets("LogSheet")
            For Each c In Target
                LR = .Cells(Rows.Count, "A").End(xlUp).row + 1
                .Cells(LR, "A").Value = ActiveSheet.Name & "!" & c.Address
                .Cells(LR, "B").Value = Now
                .Cells(LR, "C").Value = Environ("UserName")
                .Cells(LR, "D").Value = PreviousValues(c.row, c.Column)
                .Cells(LR, "E").Value = c.Value
            Next c
        End With
    End If
    Application.EnableEvents = True
    
End Sub

 

[Joe4]

Make these changes to your Worksheet_Change procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim LR     As Long
    Dim c      As Range
[COLOR=#ff0000]    Dim pval   As Variant[/COLOR]
    
    On Error Resume Next
    
    Application.EnableEvents = False
       If Target(1).Value <> PreviousValues(Target(1).Row, Target(1).Column) Then
            With Sheets("LogSheet")
            For Each c In Target
[COLOR=#ff0000]                pval = PreviousValues(c.Row, c.Column)
                If pval <> "" Then[/COLOR]
                    LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                    .Cells(LR, "A").Value = ActiveSheet.Name & "!" & c.Address
                    .Cells(LR, "B").Value = Now
                    .Cells(LR, "C").Value = Environ("UserName")
                    [COLOR=#ff0000].Cells(LR, "D").Value = pval[/COLOR]
                    .Cells(LR, "E").Value = c.Value
                End If
            Next c
        End With
    End If
    Application.EnableEvents = True
    
End Sub

 

[Flashback]

I receive error...

Sub or Function not defined for PreviousValues

 

[Joe4]

Note the code I posted JUST replaces the Worksheet_Change procedure!
You still need the global variable declaration and the Worksheet_Selection change procedure that you had in your original post (don't remove or overwrite those)!