MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Create a Unique date in a protected cell

Posted by I Moody on October 25, 1999 9:45 PM

I would like to create a date in a cell of a template or the file saved from the template so that it only displays the date entered for the first time. I need to
have the cell protected once the date is entered so users of the saved file cannot change the date.
Hope some one can help.
ian Moody

Posted by Ivan Moala on October 26, 1999 3:57 AM

One way to do it is as follows;

Private Sub Workbook_Open()
If Sheets("Sheet1").Range("A1") = "" Then
Sheets("Sheet1").Range("A1") = Now()
ActiveSheet.Protect password:="Yourpassword"
End If
End Sub

Note: since it protects this sheet and you
may want users to change data you will have to
unprotect the cells that require input.


Posted by I Moody on November 23, 1999 12:17 AM

Thanks for the reply however I am not a VBA user. from your suggestion however if you use the Now() function then every time the workbook saved from the template is opened the date will change. What I need is away to enter a date automatically when the read only template is opened and the date then to appear in the workbook saved from the template. This date remains fixed no matter when the workbook is reopened and the cell it appears in to be protected to stop the date from being altered. I hope this explains better what was required. Because of the pass word protection on the work book I cannot use the copy and paste special function.

Posted by Ivan Moala on November 24, 1999 11:30 PM

Actually the Now() will only enter the date
in this routien as a date value Once so that
it will be date stamped when opened and remain @
this date.
Perhaps someone else could have a solution
without using VBA.