SOLVED

Timestamp using VBA based on specific cell changes

Brass Contributor

Hi experts,

Hope you are well!

 

I am trying to get VBA to update a cell in one column with a timestamp of when another cell changes.

 

The attached code works well but when I use a filter on the spreadsheet, the code updates the timestamp to the current time when the actual cell has not been clicked or changed.

 

Is there anyway to better this code?

 

 

 

Function MyTimestamp(Reference As Range)

If Reference.Value <> "" Then

MyTimestamp = Format(Now, "dd-mm-yyyy hh:mm:ss")

Else

MyTimestamp = ""

End If

End Function

 

 

See attached example

 

What I would add is that ideally I would like this timestamp column to eventually consider any data updates in multiple columns. For example if any data changes in Cols A-H, column I is the timestamp of last change. Not sure if this could be added?

 

Thank you in advance

 

Matt

7 Replies

@matt0020190 

Attached is a modified version of your code.

At the same time, I add two files with an example as in the code and an example file with a timestamp in the cells as a comment.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub 'Editing multiple lines is caught
    If Target = "" Then
        Target.Offset(0, 1).ClearContents
    Else
        Target.Offset(0, 1) = CDate(Format(Now, "dd.mm.yyyy"))
    End If
End Sub

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

 

Hi, thank you for this.
The range is the best option for me, but for some reason I get the debug error :(
Excel version, operating system, storage medium, ending of the saved file, etc.
All this information is needed to have a clear picture of what and why something might not work.
On two laptops where I tried the files (both with Excel 2016) it worked without any problems.

@NikolinoDE Not sure on the issue but this is the error:

 

matt0020190_0-1715678814808.png

 

In addition, can this not be a function? I want to specify the columns to check and which cell the timestamp is output

best response confirmed by matt0020190 (Brass Contributor)
Solution

@matt0020190 

I adjusted the cell formatting again to user-defined MM/DD/YYYY hh:mm, maybe it's due to the different date settings.

Attached is the file with the customized formatting.

@matt0020190 

"the code updates the timestamp to the current time when the actual cell has not been clicked or changed"
You wrote a function, and used that function in a formula. Formulas are re-evaluated at some times even when a cell is not updated (for example, each time the workbook is opened). So a simple function + formula is not the correct design.


(Perhaps of interest to you, a function that had access to a separate store ("copy") of the values could be a valid design. But of course, that doubles the storage requirements, and you have to prevent anything but the code from updating the separate store. Too complicated.)

NikolinoDE demonstrated how code in an event handler can instead be written to react to a change in a worksheet value (due to user entry, not due to recalculation).

"Not sure on the issue but this is the error"
If you had clicked the Debug button, the VBA Editor would have opened and shown you the line of code that generated the error. (You could then take further actions to understand and possibly correct the problem.) Showing us that line of code and lines that preceded it would help us diagnose your problem.

Of course, you can use online resources to learn about debugging in the VBA Editor, such as:


"I want to specify the columns to check and which cell the timestamp is output"
Those are (or generally should be) specified in the event handler code, not in formulas on the worksheet.

However, VBA is very capable, and you could: a) reserve a cell on the worksheet (let's say K1) to contain identifiers for the range to monitor; b) reserve another cell on the worksheet (let's say K2) to contain an identifier for the column into which to place the timestamp; c) write the event handler code to retrieve and use the values from those cells.  Here's how that could look:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strRangeOfInterest  As String
    Dim rngRangeOfInterest  As Range
    Dim strColForTimestamps As String
    Dim rngColForTimestamps As Range
    '
    Dim rngAffectedCellsOI  As Range
    Dim rngCell     As Range
    Dim in4CellRow  As Long
    Dim vntCellContent  As Variant
    
    '----   Retrieve information on which cells to monitor and where
    '       to put timestamps.
    strRangeOfInterest = Range("K1").Value
    strColForTimestamps = UCase$(Range("K2").Value)
    '  --   Do some error checking.
    On Error Resume Next
    Set rngRangeOfInterest = Range(strRangeOfInterest)
    On Error GoTo 0
    If rngRangeOfInterest Is Nothing Then
        Call MsgBox("Invalid range (in K1) of cells to monitor!" _
                , vbExclamation + vbOKOnly)
        Exit Sub
    End If
    On Error Resume Next
    Set rngColForTimestamps = Range(strColForTimestamps & ":" _
            & strColForTimestamps)
    On Error GoTo 0
    If rngColForTimestamps Is Nothing Then
        Call MsgBox("Invalid column (in K2) for timestamps!" _
                , vbExclamation + vbOKOnly)
        Exit Sub
    End If
    Set rngColForTimestamps = Nothing   'We don't need it further.
    
    '----   For any cells of interest that were updated...
    Set rngAffectedCellsOI = Intersect(Target, rngRangeOfInterest)
    If Not (rngAffectedCellsOI Is Nothing) Then
        For Each rngCell In rngAffectedCellsOI
            vntCellContent = rngCell.Value
            in4CellRow = rngCell.Row
            '...Update the timestamp for the row.
            If vntCellContent = "" Then '[per your code]
                Range(strColForTimestamps & in4CellRow).Value = ""
            Else
                Range(strColForTimestamps & in4CellRow).Value = _
                    Format(Now, "dd-mm-yyyy hh:mm:ss")  '[per your code]
                    '...or just = Now() if you want to store a date- _
                    time value and do the formatting in the column
            End If
        Next rngCell
    End If

End Sub


BTW, storing timestamps as text is not a good idea (takes up more storage, cannot readily be sorted or accurately compared to other timestamps).

Side note: I strongly recommend that each of the modules into which you place VBA code (standard modules, workbook module, worksheet modules, userform modules, etc.) should start with an Option Explicit statement. That statement forces you to declare any variables you use, which avoids most bugs due to misspelling of variable names.

 

Thanks for your time explaining on the topic - most appreciated!
1 best response

Accepted Solutions
best response confirmed by matt0020190 (Brass Contributor)
Solution

@matt0020190 

I adjusted the cell formatting again to user-defined MM/DD/YYYY hh:mm, maybe it's due to the different date settings.

Attached is the file with the customized formatting.

View solution in original post