Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

TODAY () question

Posted by RoB on August 31, 2001 3:16 PM
I have created an excel template. I want to have a cell (A1 for example) that will stamp the TODAY() date in A1 ONLY the first time the file is opened. So, for example, if I opened the template today, I would have a File1.xls and A1 would be 08/31/01. Then i would save this file as something else. When i open it again, i want the 08/31/01 to stay there. How do i do this? The TODAY() command changes everytime i open the file.

Check out our Excel VBA Resources

Look at Damon's code in Archive2. (NT)

Posted by Aladin Akyurek on August 31, 2001 3:18 PM

Im looking for something a bit different

Posted by RoB on August 31, 2001 4:34 PM
This works when the sheet is calculated, but I want it to appear as soon as the sheet opens. I thought a workbook_open() would be good, but when i use this, everytime from then on, when i open a file to view it, dont make changes, and close, excel asks me if i want to save. I want to prevent this message from coming up, while at the same time putting the date in "a1" without having to put in anyother data. any ideas?

Re: TODAY () question

Posted by Ivan F Moala on August 31, 2001 6:53 PM
Private Sub Workbook_Open()
If Sheet1.[A1] = "" Then Sheet1.[A1] = Now
End Sub

Will this do ?


Re: TODAY () question

Posted by Robb on August 31, 2001 8:57 PM

Try this code in the Workbook_Open event. Do not put anything in Sheet1.A1 as the code looks for it to be empty.
If it is empty (first time workbook is opened), it inserts the current date and saves the file with its current name. You may then do whatever and save it with another name if you wish.
When you set up the file, format A1 (or whichever cell you are using) to display the date in the required format.

Open code will run each time, but will make no further changes.

Private Sub Workbook_Open()
With Worksheets("Sheet1")
If .Cells(1, 1) = "" Then
.Cells(1, 1).Formula = Now
End If
End With
End Sub

Any help?



Re: Im looking for something a bit different

Posted by Robb on August 31, 2001 8:59 PM

See my posting above


Yes, thanks that works

Posted by RoB on September 01, 2001 12:05 PM

robb...thanks but...

Posted by RoB on September 01, 2001 12:07 PM
the only problem with that is, like i said, this is a template. So...if i open it, a temp file, "file1.xls" opens until i save it. I dont want this to save until i have given it a name. I tried putting in the open, but then it caused problems when the template was opened a second time. Ivans code seems to work well...thanks for the input though!

This archive is from the original message board at
All contents © 1998-2004
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.