Timestamp when a cell in a larger range of cells is resized by a formula (Excel)
As an answer to this question, I need a timestamp in an adjacent cell whenever the cell is changed using a formula for a range of cells.
I know this means creating an array to hold the previous values in the code below (which achieves the same, but only for one cell) and would appreciate any help in achieving this.
Here's some code that works for one cell ...
In Sheet1 Cell A1 put this formula
=Sheet2!A1+1
Now in the module paste this code
Public PrevVal As Variant
Paste this into the code area of the sheet
Private Sub Worksheet_Calculate()
If Range("A1").Value <> PrevVal Then
Range("B1").Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
PrevVal = Range("A1").Value
End If
End Sub
Finally, in the Code for this book field, paste this code
Private Sub Workbook_Open()
PrevVal = Sheet1.Range("A1").Value
End Sub
source to share
You can store your previous values in Dictionary
, not an array. To use the dictionary you need to add a link to the Microsoft Scripting Executable Library
(Tools> References> Microsoft Scripting Runtime Library)
Standard module
Public PrevVal As Dictionary
ThisWorkbook Module
Private Sub Workbook_Open()
Dim r As Range
Set PrevVal = New Dictionary
For Each r In Worksheets("Sheet1").Range("A1:A10")
PrevVal.Add Item:=r.Value, Key:=r.Address
Next r
End Sub
Leaf module
Private Sub Worksheet_Calculate()
Dim v As Variant
For Each v In PrevVal.Keys()
If Range(v).Value <> PrevVal(v) Then
Range(v).Offset(0, 1).Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
PrevVal(v) = Range(v).Value
End If
Next v
End Sub
source to share