How do I get the old value of a changed cell in Excel VBA ?

How do I get the old value of a changed cell in Excel VBA ?

Asked on January 11, 2019 in Excel.
Add Comment


  • 3 Answer(s)

    Declaring a variable

    Dim oval

    Using the SelectionChange Event

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

    Here the Worksheet_Change event set

    old_value = oval
    
    Answered on January 11, 2019.
    Add Comment

         To flames a macro by using an event on the cell change.

    Try this following command :

    vNew = Range("cellChanged").value
    Application.EnableEvents = False
    Application.Undo
    vOld = Range("cellChanged").value
    Range("cellChanged").value = vNew
    Application.EnableEvents = True
    
    Answered on January 11, 2019.
    Add Comment

    Try this alternative solution :

    For the range of interest, by creating a unseen worksheet to keep the previous values .

    Private Sub Workbook_Open()
     
    Dim hiddenSheet As Worksheet
     
    Set hiddenSheet = Me.Worksheets.Add
    hiddenSheet.Visible = xlSheetVeryHidden
    hiddenSheet.Name = "HiddenSheet"
     
    //Change Sheet1 to whatever sheet you're working with
    Sheet1.UsedRange.Copy ThisWorkbook.Worksheets("HiddenSheet").Range(Sheet1.UsedRange.Address)
    End Sub
    

    After workbook is close, Delete it

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
     
    Application.DisplayAlerts = False
    Me.Worksheets("HiddenSheet").Delete
    Application.DisplayAlerts = True
    End Sub
    

    Can also changing Worksheet_Change event

    For Each cell In Target
     
       If Not (Intersect(cell, Range("cell_of_interest")) Is Nothing) Then
         new_value = cell.Value
         // here's your "old" value...
         old_value = ThisWorkbook.Worksheets("HiddenSheet").Range(cell.Address).Value
         Call DoFoo(old_value, new_value)
       End If
     
    Next cell
     
    // Update your "old" values...
    ThisWorkbook.Worksheets("HiddenSheet").UsedRange.Clear
    Me.UsedRange.Copy ThisWorkbook.Worksheets("HiddenSheet").Range(Me.UsedRange.Address)
    
    Answered on January 11, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.