Define the previous value of a cell

[Diabentes]

Hello,

The attached code does exactly what I want it to, the only problem is I don’t know how to define and also collect the previous value of the cell before it was changed. You can see the I want this to fill into column B on the ‘audit’ sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
   
    'Loop through any/all cells in 'Target' range
    For Each rngCell In Target
        addToLog rngCell
    Next rngCell
 End Sub
 
Sub addToLog(rngCell As Range)
    Debug.Print "Adding value to log: " & rngCell.Value
   
    With ActiveWorkbook.Sheets("Audit")
        Dim intNextRow As Integer 'Find the first empty row in column A of the log
        intNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

        'Add the address, old value, new value, timestamp, and user to the log
        .Range("A" & intNextRow).Value = rngCell.Address
        .Range("B" & intNextRow).Value =
        .Range("C" & intNextRow).Value = rngCell.Value
        .Range("D" & intNextRow).Value = Now()
        .Range("E" & intNextRow).Value = VBA.Environ("username")
       
    End With
End Sub

Any ideas please?

 

[davesexcel]

Possibly a Worksheet_SelectionChange event would be required as well, so when you select the cell, the value can be stored somewhere.

As you have noticed, the Worksheet_Change does not kick in until after you have entered something in a cell.

The

 

[Diabentes]

Possibly a Worksheet_SelectionChange event would be required as well, so when you select the cell, the value can be stored somewhere.

As you have noticed, the Worksheet_Change does not kick in until after you have entered something in a cell.

The

Any idea how that would look in order to link into the above code?