CODEWITHSUNDEEP
vbaexcel
Andy
Andy

Reputation: 1432

Timestamp when a cell in a larger cell range is changed via a formula (Excel)

As a follow up to this question, I need a timestamp in an adjacent cell whenever a cell is changed via a formula for a range of cells.

I'm aware this means building an array to store the previous values into the code below (which achieves the same but only for a single cell) and would appreciate any help achieving this.

Here's the code that works for a single cell...

In Sheet1 Cell A1, put this formula 

=Sheet2!A1+1

Now In a module paste this code

Public PrevVal As Variant

Paste this in the Sheet Code area

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

And lastly in the ThisWorkbook Code area paste this code

Private Sub Workbook_Open()
    PrevVal = Sheet1.Range("A1").Value
End Sub

Upvotes: 3

Views: 722

Answers (1)

CallumDA
CallumDA

Reputation: 12113

You can keep your previous values in a Dictionary rather than an array. To use the dictionary you need to add a reference to Microsoft Scripting Runtime 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

Sheet 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

Upvotes: 3

Related Questions