Automatic Populate Todays Date in Cell when File is Saved.

I am creating a user data entry spreadsheet in excel and I want to record the
date in a specific field when the excel file is opened. I need the field
"Submit Date" to be automatically populated and protected when a user opens
the file and performs a save. If the user does not Save the file the date
field should remain empty. Can anyone help with this please?

Paste the following macro into the Workbook module. You access the Workbook
module by right-clicking on the Excel icon to the left of the word "File" in
the menu across the top of the spreadsheet, select View Code.
This macro will put the data in A1 if the user commands a save. If you
want the date and time, change the "Date" to "Now". Is this what you want?
HTH Otto
"Nello" <Nello@discussions.microsoft.com> wrote in message
news:2D269EDD-CCDD-4946-86C4-3AC677397007@microsoft.com...
>I am creating a user data entry spreadsheet in excel and I want to record
>the
> date in a specific field when the excel file is opened. I need the field
> "Submit Date" to be automatically populated and protected when a user
> opens
> the file and performs a save. If the user does not Save the file the date
> field should remain empty. Can anyone help with this please?

Hi Otto

I do not see any code. Can you please resend?

"Otto Moehrbach" wrote:

> Paste the following macro into the Workbook module. You access the Workbook
> module by right-clicking on the Excel icon to the left of the word "File" in
> the menu across the top of the spreadsheet, select View Code.
> This macro will put the data in A1 if the user commands a save. If you
> want the date and time, change the "Date" to "Now". Is this what you want?
> HTH Otto
> "Nello" <Nello@discussions.microsoft.com> wrote in message
> news:2D269EDD-CCDD-4946-86C4-3AC677397007@microsoft.com...
> >I am creating a user data entry spreadsheet in excel and I want to record
> >the
> > date in a specific field when the excel file is opened. I need the field
> > "Submit Date" to be automatically populated and protected when a user
> > opens
> > the file and performs a save. If the user does not Save the file the date
> > field should remain empty. Can anyone help with this please?
>
>
>

This will do it. Read in conjunction with Otto's post. The only other thing you need to do is make sure that all of the other cells in the sheet are formatted to be unlocked or the user won't be able to save anything.

Please Login or Register  to view this content.

As for opening the worksheet, you can use exactly the same methodology, but put the code in Private Sub Workbook_Open()

Hope this helps.

Oops! I forgot to send the code. I redid it after reading your last
message. This code consists of 2 macros. The Open macro puts the date into
a cell named SubmitDate when the file is opened.
The second macro may or may not be necessary but it removes the date if the
file is closed without first being saved. Let me know if this works for
you. Otto
Private Sub Workbook_Open()
Range("SubmitDate") = Date
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = False Then _
Range("SubmitDate").ClearContents
End Sub

"Nello" <Nello@discussions.microsoft.com> wrote in message
news:569B2ECB-BD4E-4E92-86F1-B96ADD29DE80@microsoft.com...
> Hi Otto
>
> I do not see any code. Can you please resend?
>
> "Otto Moehrbach" wrote:
>
>> Paste the following macro into the Workbook module. You access the
>> Workbook
>> module by right-clicking on the Excel icon to the left of the word "File"
>> in
>> the menu across the top of the spreadsheet, select View Code.
>> This macro will put the data in A1 if the user commands a save. If
>> you
>> want the date and time, change the "Date" to "Now". Is this what you
>> want?
>> HTH Otto
>> "Nello" <Nello@discussions.microsoft.com> wrote in message
>> news:2D269EDD-CCDD-4946-86C4-3AC677397007@microsoft.com...
>> >I am creating a user data entry spreadsheet in excel and I want to
>> >record
>> >the
>> > date in a specific field when the excel file is opened. I need the
>> > field
>> > "Submit Date" to be automatically populated and protected when a user
>> > opens
>> > the file and performs a save. If the user does not Save the file the
>> > date
>> > field should remain empty. Can anyone help with this please?
>>
>>
>>