Macro for Time Stamping a Cell

[strategist]

I'm creating a worksheet to record activities at different times of the
day.

I want to record the duration of the activity

The easy way to do this would seem to be to record the start time and
end time and then calculate the duration.

The problem I'm having is creating a Macro to enable recording the
start and end times.

Approaches I've tried:

1. Using the Now() function in a macro will insert the current time
but of course this gets updated when the next time is inserted. To
overcome this I came across a suggestion to copy the cell and then,
using paste special, to paste the VALUE back into the same cell, thus
locking in the value and removing the Now() function. This works
manually but does not work as a Macro; what happens is that the Now()
function is inserted in the cell but not overwritten with the pasting
of the value. The VB window shows the following:
ActiveCell.FormulaR1C1 = "=NOW()"
Range("H5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Can anyone suggest why this may not be working and how to rectify it.


2. The other approach I came across was to use the keystrokes
Ctrl +Shift+;
Manually this inserts the time into the cell but it appears that this
cannot be recorded. Reviewing the recording shows no trace of the keys
used.

My question on this one: Is there way to record these keystrokes so
that I can put this in a macro.

Background data:
OSX 10.4.8
Excel v.X

Thanks

 

[JE McGimpsey]

My question on this one: Is there way to record these keystrokes so
that I can put this in a macro.

You can't record keystrokes because the recorder only records the
results of operations, not the operations themselves.

Also not sure why you'd need to record a macro, rather than just use the
keyboard shortcut.

If you're going to use a macro, however, using VBA's built-in Now
function would be better:

Public Sub RecordTime()
With ActiveCell
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
End Sub

Substitute your favored number format.

 

[strategist]

Thanks very much for your response

Also not sure why you'd need to record a macro, rather than just use the
keyboard shortcut.

I'm creating a list in which people have to enter a value in each
column. Several of the columns will have pop up lists so I want to make
the operation as consistent as possible.

If you're going to use a macro, however, using VBA's built-in Now
function would be better:

Public Sub RecordTime()
With ActiveCell
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
End Sub

Substitute your favored number format.

Thanks for creating this.
It works perfectly
One note for anyone else who uses this. I copied the code directly from
the web page and pasted it into the editor. This caused some syntax
problems that I was not smart enough to resolve but retyping the
instructions seemed to remove all the problems and it works fine.

Many thanks.