Time

[Tom]

I need to be able to click on a cell and have it be populated with the
current HH:MM:SS. I know ctrl+shift+; will do the job but I need it to be
even more simple than that for the end users.

 

[JE McGimpsey]

One way:

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Range("J1")
If Not Intersect(Target, .Cells) Is Nothing Then
.Value = Time
.NumberFormat = "HH:MM:SS"
End If
End With
End Sub

or, if you only want it to update once:


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Range("J1")
If Not Intersect(Target, .Cells) Is Nothing Then
If IsEmpty(.Value) Then
.Value = Time
.NumberFormat = "HH:MM:SS"
End If
End If
End With
End Sub

Note that this will also fire if the user presses an arrow key to enter
the cell. If you'd rather, you can use a double-click:


Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Range("J1")
If Not Intersect(Target, .Cells) Is Nothing Then
If IsEmpty(.Value) Then
.Value = Time
.NumberFormat = "HH:MM:SS"
Cancel = True
End If
End If
End With
End Sub