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]

Why VBA?

Why not just put the formula:

=NOW()

in the cell you want this in?

That will automatically update with every keystroke.

 

[Keebler]

that is what i have, but id like it to always be current with no user intervention
and it does not update with every keystroke (at least for me) unless i click the box it is in, then click the formula, then hit enter

 

[Keebler]

OR, maybe it would be easier
if there as a VBA to place the current date/time in an adjacent cell only when data is entered

 

[Keebler]

so this is where i am at with the vba (it is not working)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("e:e").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "d").Value = Now
Else
Cells(Cell.Row, "d").Value = ""
End If
End If
Next Cell
End Sub

column E has the data and column D is where i want the date/time

i got it!
i changed this line to be
Cells(Cell.Row, "d").Value = Now
Cells(Cell.Row, "d").Value = Now()

now it works !

i have the column set
by format cells
custom
m/d/yyy hh:mm

 

[Keebler]

of course using

Cells(Cell.Row, "d").Value = Format(Now(), "dd/mm/yyyy hh:mm")

eliminates the need for the column formatting

 

[Joe4]

of course using

Cells(Cell.Row, "d").Value = Format(Now(), "dd/mm/yyyy hh:mm")

eliminates the need for the column formatting

Just note that will return a Text entry, and not a valid Date entry.
That may not matter, if it is just for visual purposes and you are not using it in calculations.

Your code looks a little odd to me. Note that only the current row is going to get updated with the current date/time.
Not really sure exactly what your structure is, how many rows of data you are dealing with, and what you want updated when.

Are you really just looking for a date stamp to capture the current date/time a specific cell was updated?
If so, there are much better ways of doing it (and about a million questions on the topic!).

 

[Keebler]

i am wanting a date/time stamp for the adjacent cell only BUT
i would prefer an actual date/time not just a text entry as you mentioned

any advise/direction you have would be greatly appreciated

im just learning vba, and i make a lot of mistakes, so any help is greatly appreciated

 

[Joe4]

OK, the following code will update column D with the current date/time anytime you manually update the value in the corresponding column E.

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
'       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"
    Next cell
    Application.EnableEvents = True
    
End Sub

Note that I added some documentation to help explain the steps.

 

[Keebler]

Joe4,
thank you.
can you explain to me how your vba is different other than layout?
is it still producing a text output? or is it a valid date like you mentioned earlier?
and i dont want the whole column to update, only the adjacent cell