Time Stamp when cell value is changed

SantanaKRE8s

Board Regular
Joined
Jul 11, 2023
Messages
131
Office Version
  1. 365
Platform
  1. Windows
I have a VBA code to add a time stamp when ever the value of a cell is changed, but I am using a Count function in the cell, so I noticed the time stam is not working. Is there a way to make it work with the Function so that this is being modified automatically.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyDataRng As Range
Set MyDataRng = Range("C8:C13")

If Intersect(Target, MyDataRng) Is Nothing Then Exit Sub

On Error Resume Next
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 1) = Now
End If

Target.Offset(0, 2) = Now

End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
The COUNTIF function is in which cell?
Is it in just one cell, or every cell in the range C8:C13?

Note that Worksheet_Change event procedures only run when cell values are manually updated, not changes due to formulas being re-calculated.
There is a Worksheet_Calculation event, but that cannot pinpoint which cell was re-calculated, only that some cell somewhere on the sheet was re-calculated.
 
Upvote 0
I have diferent count functions in C8, C9, C10, C11, C12, C13, depending on what Im counting.
 

Attachments

  • TIME STAMP.png
    TIME STAMP.png
    48.5 KB · Views: 5
Upvote 0
Can you post what those formulas actually look like?
If we can see exactly how they are all structured, we may be able to advise you of another way of doing this.
 
Upvote 0
C8 - =COUNTA(SPX_BCKLG[PO ID])
C9 - =COUNTIF(SPX_BCKLG[LN Sts],"BO")
C10 - =COUNTIF(SPX_BCKLG[CURRENT],"<="&TODAY())
C11 - =COUNTIF(SPX_BCKLG[LN Sts],"DE")
C12 - =COUNTIF(SPX_BCKLG[[ ST]],"In Progress")
C13 - =COUNTA(RESOLVED!W3:W1048576)
 
Upvote 0
If the values in the named ranges are being entered/changed manually, you can watch those ranges for changes, and update the appropriate time stamp based on that, i.e. watch the range "SPX_BCKLG[PO ID]" with a Worksheet_Change event and if there is a change in that range, then update the time stamp in cell D8, etc.
 
Upvote 0
Joe4, Im sorry I did not understand your reply. The reason why I wanted to be automatically updating on its own is because that information is going to be going into a userform. But Im not sure if your giving me a solution or if this not possible. Sorry I am barely learning.
 
Upvote 0
For instance I just ran a test Ip dated the value in the main sheet "BACKLOG" and the count function updated the count but the time stamp for Date updated did not change. Im thinking I have to someway tell timestamp VBA to look at the value and not the function.
 
Upvote 0
Joe4, Im sorry I did not understand your reply. The reason why I wanted to be automatically updating on its own is because that information is going to be going into a userform. But Im not sure if your giving me a solution or if this not possible. Sorry I am barely learning.
This is pretty advanced stuff for someone who is just learning!

Basically, Worksheet_Change event procedures only fire when data is manually updated (and when I say "manually updated", I mean by any means other than a formula - either a user is manually entering those values in the cells, they are being Copy/Pasted in manually, or VBA is entering new values in those cells). All cells that contain formulas will NEVER trigger Worksheet_Change event procedures! It does NOT monitor formula values that change.

Worksheet_Calculate event procedures WILL fire when the values being returned by formulas change. However, they will not help you here because unlike Worksheet_Change events, they CANNOT tell which cells value changed, just that some cell somewhere on the sheet changed. So this is not going to be helpful to you, as you cannot pinpoint which time stamp should be updated.

The workaround which MAY sometimes work (depending on your conditions) is to watch the cells your formulas are referencing with Worksheet_Change events.
For example, your first formula in cell C8 is:

Excel Formula:
=COUNTA(SPX_BCKLG[PO ID])
So, the formula is counting records in your range "(SPX_BCKLG[PO ID])". If the values in that range are being manually updated, then what you can do is to create a Worksheet_Change event procedure block of code that watches THAT range ("SPX_BCKLG[PO ID]"), and if one happens, then update the time stamp in row 8.

You would have to do that for each of the ranges your formulas in C8:C13 reference. Because you are only allowed to have one Worksheet_Change event procedure code per sheet, you would have to have one of these, with 6 different blocks of code, one for each combination of range/conditions.
 
Upvote 0
Thank you Joe4, you are right its a bit complicated. But I apreceiate the help. If you come up with something more simple let me know.
 
Upvote 0

Forum statistics

Threads
1,223,854
Messages
6,175,018
Members
452,602
Latest member
Luka Vladimir

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