Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a file with a VB macro that puts a TODAY() formula in a cell when I click a button. The formula is volatile and updates every time the sheet is calculated. Instead, I would like to lock the value so that it only shows the date on which the macro was executed. So, if I open the file tomorrow, it will be the same value. How can I do this?

share|improve this question
bAN is looking to prevent the Excel function =TODAY(), which is inserted into his sheet by a macro, from updating. Rather than =TODAY(), he's looking for a formula that will enter a date/time stamp and leave it. – Jared Harley Jul 30 '09 at 19:23
up vote 6 down vote accepted

Use the value property of the cell:

TheCell.Value = Date

Date = Today

share|improve this answer

You'll have to go in and modify the VBA code in the Click event off the button. I'd create a second button if you want Today's date in the cell, and disable it from the first button.

share|improve this answer

Quick way of doing this -

Select the cell and press Ctrl + ;

Another trick - Under the same conditions, if you want to insert the current time in HH: MM format:

Press Ctrl + : in the cell.

(FOR MAC: cmd + ;)

share|improve this answer
The question asks how this is done inside a macro, not from the keyboard. – Chenmunka Feb 12 at 15:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .