VBA - Track/log changes - Store and retreive values from several cells - Small change needed

[lovallee]

Hello,

Setup: I use the two macros below to log changes (i.e. keep an audit trail) in a worksheet (using Excel's "Track changes" tool forces me to share the workbook...and sharing comes with loads of limitation...).

Issue: It works great except for one thing: if I paste new data over a range a cells (i.e. variable "Target" contains more than one cell), the 2nd macro variable "Previous value" only returns the value of the first cell over which new data was pasted.

Question: How can I modify the code so that variable "Previous value" is stored as an array of all the selected cells in the first macro and retreived as such in the second macro?

Thanks!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Store current value of seleted cell
Application.EnableEvents = True
PreviousValue = Target.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.Value <> PreviousValue 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 = PreviousValue
        .Cells(LR, "E").Value = c.Value
        Next c
    End With
End If

Application.EnableEvents = True

End Sub

 

[AlphaFrog]

Try this...

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

 

[lovallee]

Thanks AlphaFrog!

I does not seem to work either but I will continue to work around it and post a solution if I find one...

 

[AlphaFrog]

It worked for me for what it's worth.

What did it do; error, nothing, wrong values?
Does your used range start at cell A1?

 

[lovallee]

I changed it to the code below but issue seems to be that the first macro does not store an array of value in variable "PreviousValues"...Should I use VBA's Array() function?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Store current values of all cells
    Application.EnableEvents = True
    PreviousValues = Target.Value2
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
    For Each c In Target
        If Target(c).Value <> PreviousValues(c) Then
            With Sheets("LogSheet")
                     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)
                    .Cells(LR, "E").Value = c.Value
            End With
        End If
    Next c
    Application.EnableEvents = True
    
End Sub

 

[lovallee]

Sorry AlphaFrog...It did work after all! I had copied in the wrong sheet...
Thanks a million!

 

[lovallee]

Update: I slightly adapted the code as per below to
1) only log actual changes when several cells are pasted (i.e. cells for which value has not changed will not trigger a log entry).
2) log changes resulting from an "undo"
It starts to behave like Excel's "Track changes" tool, but without the need to share workbook!

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

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

'Re-store values in case of an "undo" (which do not trigger Worksheet_SelectionChange)
   PreviousValues = Me.UsedRange.Value

Application.EnableEvents = True
End Sub

 

[lovallee]

Hello again,

I now realise that the follow code (see previous posts) is slowing down enormously the selection of cells in large worksheets. For example, selecting the next cell by clicking, say "arrow down" or "arrow right" will result in Excel taking about a second or two to actually select the next cell.

Question: Is there a way to replace "UsedRange.Value" by something more efficient? or otherwise speed up the process? Thanks!

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

 

[crookesa]

@lovallee Did you get a solution to this that didn't slow down your workbook? I'm trying to get it to work also if a range of cells are pasted into rather than just a single cell