Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Comment get the old value of a changerd cell in Excel VBA?

ayi

I'm detecting changes in the values of certain cells in an Excel spreadsheet like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim old_value As String
Dim new_value As String

For Each cell In Target

    If Not (Intersect(cell, Range("cell_of_interest")) Is Nothing) Then
        new_value = cell.Value
        old_value = ' what here?
        Call DoFoo (old_value, new_value)
    End If

Next cell

End Sub

Assuming this isn't too bad a way of coding this, how do I get the value of the cell before the change?

ayi

try this

declare a variable say

Dim oval

and in the SelectionChange Event

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
oval = Target.Value
End Sub

and in your Worksheet_Change event set

old_value = oval