Yes, that way only seems to enter time to the nearest minute.
Right-click the sheet's tab, choose View code, then paste this code where the flashing cursor is (assuming you don't already have a change event for that sheet):
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
TimeGrab = Now
Static IsBlocked As Boolean
If Not IsBlocked Then
IsBlocked = True
Set ChangingCells = Intersect(Target, Columns(1))
If Not ChangingCells Is Nothing Then
For Each cll In ChangingCells.Cells
With Cells(cll.Row, "F")
.Value = TimeGrab
.NumberFormat = "h:mm:ss" '.NumberFormat = "dd/mm/yyyy hh:mm:ss"
End With
Next cll
End If
IsBlocked = False
End If
End Sub
Now go back to the sheet and test. If it's ok then close the newly opened application, save the Excel workbook and you're done.
This macro actually pastes the time
and date, but formats the cell to show only time, so date information remains in the cell. If you want truly only to have time in the cell, change:
TimeGrab = Now
to
TimeGrab = Time
and you can also lose the line beginning
.NumberFormat.
On the other hand, you may want to keep the date info and see it, so retain the:
TimeGrab = Now
line and use
.NumberFormat = "dd/mm/yyyy hh:mm:ss"
as the
.NumberFormat line
although you might want to adjust
dd/mm to
mm/dd if you're in the US - very confusing - I tend to use a date format with
mmm in so that the month appears as letters rather than a number.