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
        }
    }

Answer 1

I'm afraid to achieve your goal monitoring all sheet cells you have to:

• make a "mirror" copy of the whole "base" sheet

  each cell of which will have a reference to the corresponding cell in the "base" sheet (i.e. "mirror" sheet A1 cell will have "="baseSheetName!A1" formula", and so on)

• set Application.Calculation = xlCalculationManual before any changing in "base" sheet (possibly set it as default configuration of your workbook at its opening)

• use Target argument of Worksheet_SelectionChange() event handler to select corresponding "Mirror" sheet cells that, thanks to Application.Calculation = xlCalculationManual setting, will still have previous value

If your concern is about a limited number of "base" sheet cells, you can go on in a similar way but keeping "mirror" cells in the "base" sheet itself

In this latter case here's a code to handle it (NOTE: VBA code, but you can easily translate in C#")

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sensitiveRange As Range
Dim sensitiveRangeSelected As Range

Set sensitiveRange = Range("sensitiveRange")
Set sensitiveRangeSelected = Application.Intersect(sensitiveRange, Target)
If sensitiveRangeSelected Is Nothing Then
    ' no 'sensitive' cells  --> go ahead
Else
    ' 'sensitive' cells !! -> add code to handle thier value or store it in some array
End If

End Sub

where you have to set a named range (I called it "sensitiveRange") in your "base" sheet with all its cells that must be tracked

Answer 2

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