+ Reply to Thread
Results 1 to 4 of 4

Make date and time columns static after hitting Return

  1. #1
    Registered User
    Join Date
    01-10-2012
    Location
    Montreal
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question Make date and time columns static after hitting Return

    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.

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Make date and time columns static after hitting Return

    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.
    Last edited by mikerickson; 01-10-2012 at 02:43 AM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    01-10-2012
    Location
    Montreal
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question Re: Make date and time columns static after hitting Return

    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.

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Make date and time columns static after hitting Return

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1