Excel: Previous Values from Ranges

Question

I would like to get previous cells values when I copy single cell and paste it to multiple cells. The How can I determine new & previous cell value on SheetChange event in Excel? is good enough for detecting single cell previous value. However when I am trying to copy one cell (ctrl+v, dragging etc) and apply it to multiple cells, none of the previous values are detected. Instead, the array of values are equal to the first cell, which leads me to the conclusion that cells are changed before SheetSelectionChange event occures. Any idea how to handle this?

private void Application_SheetSelectionChange(object Sh, Excel.Range Target)
    {
        try
        {
            if (Target.Value2 != null)
            {
                foreach (Excel.Range range in Target)
                {
                   // Each range in Target has same value as first value instead of previous value
                }
            }
        }
        catch (Exception ex)
        {
           // Log stuff
        }
    }

This thread will help you. ( stackoverflow.com/questions/35617755/… ) — Sixthsense
You can use Application.Undo to get the previous value(s). In VBA : stackoverflow.com/questions/35018093/… — Tim Williams
@TimWilliams Unfortunately Undo is not prefered as it has side effect of getting back to previous cell when you click enter, causing user frustration — Jim
@Sixthsense I am not sure your solution can be applied as it seems that the SheetSelectionChange doesn't detect propertly paste of values. Meaning that the event is fired after the values are changed — Jim
Then you should combine both sheet_change and selection_change events. — Sixthsense

Tim Williams Tim Williams · Accepted Answer · 2016-02-29T17:12:32

You can grab the selection prior to running the Undo, and restore it at the end of the process.

Note: the Select at the end will fail if the sheet isn't active (in the case of a sheet being updated by code for example) so you might need to check for that.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Where As String, OldValue As Variant, NewValue As Variant
    Dim r As Long, c As Long, tmp
    Dim sel As Object '<<< current selection: not always a Range!
    Dim rngTrack As Range

    On Error GoTo haveError
    Application.EnableEvents = False
    Set sel = Selection '<<< capture the selection
    Where = Target.Address
    NewValue = Target.Value
    Application.Undo
    OldValue = Target.Value 'get the previous values
    Target.Value = NewValue
    Application.EnableEvents = True

    Set rngTrack = Sheets("Tracking").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

    'set some limit for the size of change you want to track
    If Target.Cells.CountLarge < 1000 Then

        'convert single-cell values to array...
        If Target.Cells.CountLarge = 1 Then
            OldValue = ToArray(OldValue)
            NewValue = ToArray(NewValue)
        End If

        'multi-cell: treat as arrays
        For r = 1 To UBound(OldValue, 1)
        For c = 1 To UBound(OldValue, 2)
            If OldValue(r, c) <> NewValue(r, c) Then
                rngTrack.Resize(1, 3).Value = _
                  Array(Target.Cells(r, c).Address, OldValue(r, c), NewValue(r, c))
                Set rngTrack = rngTrack.Offset(1, 0)
            End If
        Next c
        Next r
    End If

    sel.Select '<<< reset the selection
    Exit Sub

haveError:
    Application.EnableEvents = True

End Sub
'utility function
Private Function ToArray(v)
    Dim rv(1 To 1, 1 To 1)
    rv(1, 1) = v
    ToArray = rv
End Function

Excel: Previous Values from Ranges

2 Answers