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

Question

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

Instead you can use worksheet_change event to detect change in a sheet (any range of data) and add timestamp in the sane row changed. You may use Target.row to identify the changed row. — ArindamD, Apr 03 '17 at 13:57
Change to `PrevVal = application.sum(Range("A1:A9").Value)` in both places. Then if any value in A1:A9 changes, the sum of all of the values will change. — , Apr 03 '17 at 14:05
oh, and change to `Range("B1").Value = Now: Range("B1").Numberformat = "dd/mm/yyyy hh:mm:ss"`. — , Apr 03 '17 at 14:10
I tried that but while it updated the timestamp in B1 for any change in the range A1:A9, I want a separate timestamp adjacent to every cell that changes. — Andy, Apr 03 '17 at 14:13
@Jeeped, there are plenty of occasions when that wouldn't work. To name a few: if the numeric changes offset each other or if there were non-numeric values. OP also wants to know exactly which values have changed - not just whether or not they've changed. — CallumDA, Apr 03 '17 at 14:23
@CallumDA - and that is why it was a comment and not posted as a solution. If the OP decides that it is addequate then it is less calculation intensive (and a little easier to follow) than a variant array or scripting dictionary solution. — , Apr 03 '17 at 14:25
@Andy - Sorry, I simply did not infer that from your title or narrative. — , Apr 03 '17 at 14:26
No need to apologise, I can see a usage case for it in other instances. — Andy, Apr 03 '17 at 15:24

CallumDA · Accepted Answer · 2017-04-03T14:42:06.683

3

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

edited Apr 03 '17 at 14:42

answered Apr 03 '17 at 14:03

CallumDA

12,025
6
30
52

Nice answer - do you need to `Remove` and then `Add` again ? – Robin Mackenzie Apr 03 '17 at 14:08
@RobinMackenzie thanks - I actually can't test this code because I'm working on a Mac. I had a quick look to see if you can change the value of an item in a Dictionary given its key and didn't come up with anything. Still looking though :) – CallumDA Apr 03 '17 at 14:12
1

Overwriting the keys' item value should be sufficient. – Apr 03 '17 at 14:27
When I update a cell, I get an error after running Debug it highlights this line in the code - For Each v In PrevVal.Keys() – Andy Apr 03 '17 at 15:28
1

@Andy Did you save and close/reopen? because there's nothing in the dictionary until you run the Workbook_Open sub (i.e. when you open your workbook) – CallumDA Apr 03 '17 at 15:31
1

Thanks, I didn't and now I have it works perfectly. Thanks again. – Andy Apr 03 '17 at 15:33
As a followup, can I use this in multiple sheets? Currently it's just for Sheet 1 but what if I want it in Sheet 5 and Sheet 6 as well? – Andy Apr 03 '17 at 19:47
1

@Andy, create three public dictionaries and paste the code into each of the sheet modules, change the code to refer to the specific dictionary for that sheet – CallumDA Apr 03 '17 at 20:18
Do I create those extra public dictionaries by adding additional modules? – Andy Apr 03 '17 at 20:22
@Andy, no just three lines in the same module will be fine :) – CallumDA Apr 03 '17 at 20:23
So just to clarify, I post Public PrevVal As Dictionary three times in the standard module? What about the This Worksheet part of the code? – Andy Apr 03 '17 at 20:26
1

Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/139804/discussion-between-callumda-and-andy). – CallumDA Apr 03 '17 at 20:26
Big props to @CallumDA for the help. – Andy Apr 03 '17 at 20:57

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

1 Answers1

Linked