Make date and time columns static after hitting Return

[soireesmagiques]

In Excel 2003 I have a formula in column D which gives today's date and column E which gives the time. When I'm on, let's say line 7, how can I make cells D7 and E7 copy/paste special as values when I move to cell D7? In other words, I'm building some sort of a log and formula in column D is =now() but I want that date to remain static. And so for column E with the time.

I tried the following, but it seems that the vbKeyReturn is not working :

Private Sub Text1_KeyDown(KeyCode As Integer)

If KeyCode = vbKeyReturn Then
With ActiveWorksheet
.Range("D&ActiveCell.RowAdress&:&E&ActiveCell.RowAdress&").Select
.Range("D&ActiveCell.RowAdress&:&E&ActiveCell.RowAdress&").Copy
.Range("D&ActiveCell.RowAdress&:&E&ActiveCell.RowAdress&").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=FalseCall
Application.CutCopyMode = False

End With
End If

End Sub

Thanks.

[mikerickson]

You have formulas in columns D and E.
Put this in that sheet's code module and double-clicking on a cell with a formula will put the value in the cell, (the same result as Copy/PasteSpecialValues, but using different methods)
Double click it again, and the formula will be restored.

Please Login or Register  to view this content.

[soireesmagiques]

Thanks mikerickson for such a quick answer

But do you think it would be possible that the copy/paste occurs just by pressing tab on the previous cell (column C)... this would avoid me grabbing the mouse and would allow for faster data entry.

Thanks.

[mikerickson]

Put this in a normal module, run the sub Test, thereafter, if the active Cell is in column C, D or E, pressing Cmd+Tab will change the formulas in C:D of that row to values.

Please Login or Register  to view this content.