Timestamp that updates automatically when a cell with a formula changes

[GerMae]

Hello, I've spent hours trying to get this to work and I keep finding similar issues, but not my exact one. I have a worksheet that I am tracking an inventory count in and I would like the timestamp to update automatically when the count changes basically to represent an "effective date" of the new count.

Worksheet Setup

• There is a starting count in A1 (entered manually)
• A2 has a formula that counts the X's from another worksheet (used inventory)
• A3 has a formula A1-A2

I would like the time stamp to be in B3 to update each time the number changes in A3. I would even be satisfied if it updated each time A2 changed

I'm current using the below VBA BUT it keeps erroring out at Application.Undo

Sub Worksheet_Change(ByVal Target As Range)

Dim myCount As Range
Dim myEffTime As Range
Dim myUpdtdTime As Range
Set myCount = Range("A3:A4")

For Each cell In myCount
Dim OldValue As Variant
'Application.EnableEvents = False
Application.Undo
OldValue = cell.Value
Application.Undo
Application.EnableEvents = True
If OldValue <> cell.Value Then
Set myEffTime = Range("B3")
Set myUpdtdTime = Range("B4")
If myEffTime.Value = "" Then
myEffTime.Value = Now
End If
myUpdtdTime.Value = Now
End If
Next cell
End Sub

 

[Joe4]

Welcome to the Board!

You cannot use a "Worksheet_Change" event procedure to do this. Worksheet_Change event procedures are only fired when a cell is manually updated on a sheet; it does not fire when the value being returned by a formula changes.

The "Worksheet_Calculate" event procedure fires whenever a calculation of the sheet is updated. However, this event procedure has one big drawback. It CANNOT identify the cell/formula whose value changed. All it can do is tell you that some calculation somewhere on the sheet changed. So this probably will not be helpful to you unless A2 and A3 are the only formulas on the entire sheet. If they are the only formulas on the entire sheet, then you can use this. Otherwise, it probably won't be helpful in your goal.

The only thing I can really recommend is to put a Worksheet_Change event procedure on the other sheet where the X's are occurring. Of course, this will only work if the X's on that sheet are being entered manually. If they are, then you can use a Worksheet_Change event procedure to capture that update, and then go to your other sheet and update your timestamp.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "A2" Then Exit Sub
    Range("B3") = Now
End Sub

 

[Joe4]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "A2" Then Exit Sub
    Range("B3") = Now
End Sub

Mumps, that will only work if cell A2 MANUALLY changes, right?
It will not fire if A2 contains a formula, and the changes that occur in that cell are due to a formula updating.

 

[mumps]

@Joe4
You are perfectly correct. My apologies. This should work if A2 and A3 are the only formulas on the entire sheet as you suggested.

Private Sub Worksheet_Calculate()
    Range("B3") = Now
End Sub

 

[GerMae]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "A2" Then Exit Sub
    Range("B3") = Now
End Sub

Thank you so much! I will try this one and let you know. Yes, the calculations in A2 & A3 are the only formula on the worksheet.

 

[GerMae]

@Joe4
You are perfectly correct. My apologies. This should work if A2 and A3 are the only formulas on the entire sheet as you suggested.

Private Sub Worksheet_Calculate()
    Range("B3") = Now
End Sub

 

[GerMae]

This worked perfectly! Thank you much!

 

[mumps]

You are very welcome.