Audit trail macro doesn't register drag and fill usage

[Prototype_79L]

Hello!

I have an excel file, which needs to be monitored, Which user changed which cell, previous and new values with time stamps.

I found a macro in the older forums which works fine except drag and fill.
When drag and fill is used, the log sheet doesn't register the change.

I narrowed it down to the code thinking the previous value and the target valu is the same, and skips the logging part.

Can this problem be solved or should i disable the drag and pull option?

See the code below:


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

[Prototype_79L]

I found the root of the problem:
If you use autofill as a VBA commad, you have to define a source range and an output range.
The output range must contain the source range, to be able to predict the autofill condition.

For example if i want to copy A5 with drag fill to A6:A10 then my Source range is A5, my output range is A5:A10, thus the first cell of the target is the same as the source

My code compares only the first cell of target to the previous values, and since they are equal because of autofill, the code skips the log.

Had to modify the comparison with a cell counter, now works:

Application.EnableEvents = False

With Sheets("LogSheet")
For i = 1 To Target.Cells.Count
If Target(i).Value <> Previousvalues(Target(i).Row, Target(i).Column) Then
LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(LR, "A").Value = ActiveSheet.Name & "!" & Target(i).Address
.Cells(LR, "B").Value = Now
.Cells(LR, "C").Value = Environ("UserName")
.Cells(LR, "D").Value = Previousvalues(Target(i).Row, Target(i).Column)
.Cells(LR, "E").Value = Target(i).Value
Else
End If
Next i
End With