Always display the current day/time

[Keebler]

Is there a vba code that can update a cell say every minute (while the worksheet is open) to reflect the current date/time?
what i am wanting is a cell (a2 in this case) to auto update (can even update on mouse movement, keystrokes, or every minute - as long as it is always accurate)

4/3/2024 09:36 ​

 

[Joe4]

can you explain to me how your vba is different other than layout?

The biggest difference is I am using the "Worksheet_Change" event. That only runs when a cell is manually updated.
Your code uses the "Worksheet_SelectionChange" event, which runs whenever a cell is selected, regardless of whether or not any update is made.

is it still producing a text output? or is it a valid date like you mentioned earlier?

No, mine produces a valid date. Note how I am applying a number format to the cell. Cell formatting can only be applied to valid number/date entries. It does not work on text entries.
What made your result text was the use of the FORMAT function. The FORMAT function in VBA returns a Text entry.

and i dont want the whole column to update, only the adjacent cell

And that is exactly what it does. Note that the "Worksheet_Change" and "Worksheet_SelectionChange" have "Target" parameters. This is the range which "fires" the event.
In "Worksheet_Change", it is the range that is manually updated. Most often, it is a single cell, but could be a multi-cell range if you used copy/paste to paste to a multi-cell range.
In "Worksheet_SelectionChange", it is the range that is selected (which could also be one or more cells).

When I am looping through the cells in my code, I am NOT looping through the entire column. I am simply looping through all the cells in column E that were JUST manually updated.
And then it is updating column D of that particular updated row(s).

Hope that helps to clear things up.
Let me know if you have any other questions.

 

[Keebler]

fantastic
it works, but!
if i delete the data (such as when testing) the time stamp does not erase (). it is NOT a big deal. i can delete the time stamp in col D.

can there be a
Cells(cell.Row, "D").clearcontents
after
Set rng = Intersect(Target, Columns("E:E"))
If rng Is Nothing Then Exit Sub

 

[Joe4]

Simple enough change:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim rng As Range
    
'   See if any cells updated in column E
    Set rng = Intersect(Target, Columns("E:E"))
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells in column E
    Application.EnableEvents = False
    For Each cell In rng
        'Erase date stamp if entry blank
        If cell.Value = "" Then
            Cells(cell.Row, "D").ClearContents
        Else
'           Update column D with current date/time
            Cells(cell.Row, "D").Value = Now()
'           Format cell with valid date/time format
            Cells(cell.Row, "D").NumberFormat = "dd/mm/yyyy hh:mm"
        End If
    Next cell
    Application.EnableEvents = True
    
End Sub

 

[Keebler]

Thank you joe4
sorry for the delay responding. was called out of town

 

[Joe4]

You are welcome.
Glad I was able to help.