Define the previous value of a cell

Diabentes

New Member
Joined
Jun 15, 2023
Messages
5
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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.

VBA Code:
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top