MrExcel.com - Your One Stop for Excel Tips & Solutions

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.

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

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

Posted by RoB on August 31, 2001 4:34 PM

Im looking for something a bit different

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?

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 ?


Ivan

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

Rob

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
ActiveWorkbook.Save
Else
End If
End With
End Sub

Any help?

Regards

Robb

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

Re: Im looking for something a bit different

Rob

See my posting above

Regards

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

Yes, thanks that works

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

robb...thanks but...

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 activeworkbook.save 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!