Time Stamp when cell value is changed

[SantanaKRE8s]

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

 

[Joe4]

You can only have one "Worksheet_Change" event procedure per sheet, so you have to combine them into one, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'***BLOCK 1***
Dim rng As Range
Dim cell As Range

' See if any updates to column S
Set rng = Intersect(Range("X:X"), Target)

' What to do if update in column X
If Not rng Is Nothing Then

    ' Loop through values updated in column S
    Application.EnableEvents = False
    For Each cell In rng
        ' See if cell value is Resovled
        If cell.Value = "Resolved" Then
            ' Copy to bottom of resolved sheet
            Rows(cell.Row).Copy
            Sheets("Resolved").Range("A" & Sheets("Resolved").UsedRange.Rows.Count + 1).PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            ' Remove from backlog sheet
            Rows(cell.Row).Delete
        End If
    Next cell
    Application.EnableEvents = True
End If

'***BLOCK 2***
' Update cell Z1 with any cell change
Application.EnableEvents = False
Range("Z1").Value = Now
Application.EnableEvents = True

End Sub

 

[Joe4]

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.

 

[SantanaKRE8s]

I have diferent count functions in C8, C9, C10, C11, C12, C13, depending on what Im counting.

 

[Joe4]

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.

 

[SantanaKRE8s]

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)

 

[Joe4]

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.

 

[SantanaKRE8s]

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.

 

[SantanaKRE8s]

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.

 

[Joe4]

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:

=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.