Inserting current time

  • Thread starter Thread starter Talltone
  • Start date Start date
T

Talltone

I am trying to insert times into a Excel 2000 spreadsheet
in the format hh:mm:ss. Because a number of events have
to be logged, some very close together, I am trying to use
the CTRL + SHIFT + : (colon) technique. I have formatted
the cells to show hh:mm:ss but when I use the key
combination above, it only gives me hh:mm. When I press
RETURN to move to the next cell the cell data then changes
to hh:mm:00.
In other words I cannot get it to record the seconds.
Please can somebody help! I seem to be close to the
solution but not close enough! The computer clock shows
seconds, by the way.
 
CTRL-Shift-: will only insert minutes. If you want it to insert
seconds, the way I do it is to put these in a workbook in your
XLStart folder (e.g., Personal.xls),:


in the ThisWorkbook module:

Private Sub Workbook_Open()
Application.OnKey "+^:", "CtrlShiftColon"
End Sub


and in a regular code module:

Public Sub CtrlShiftColon()
With Selection
.NumberFormat = "hh:mm:ss"
.Value = Time
End With
End Sub
 
Talltone

How about putting a CommandButton from the control toolbox on the worksheet.
(Right click anywhere on the toolbars and select the Control Toolbox.
Select a command button from this and draw it on the sheet. While still in
design mode, right click the control and select 'properties'. Set the
TakeFocusOnClick setting to 'false'. Double click the control and paste the
code below in to the resultant window. Close the window, exit 'design
mode', (click on the little set square icon))

Now each time you click on the button, it will put in the current time,
including seconds and advance to the cell below.

Code

Private Sub CommandButton1_Click()
ActiveCell.Value = Time()
ActiveCell.Offset(1, 0).Select
End Sub

(oooohhhhhh, it was sooooooooooooo satisfying to use select! (It is seldom
necessary to use select, you can act on objects in VBA without
selecting...it's just been such a long time!))
 
Even though J.E. recommended putting the code in your personal.xls file (that
will probably remain open as long as you have excel open), I think I might add
this, too:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "+^:"
End Sub
(again in the thisworkbook module)

It resets the ctrl-shift-colon back to normal right before you close that
workbook.
 
Back
Top