Date updated upon save

[Jennifer Marrs]

Hi,

I looked and couldn't find this answer, so if this question has already been
answered I apologize in advance.

I would like to create a field in my spreadsheet that automatically updates
the date and time upon saving. I found the Now() and Date() functions but I
couldn't find anything that updates upon saving. I don't know a lot about
scripting but I'd be willing to give that a shot if that is what it takes.

Jennifer Marrs

 

[Bob Greenblatt]

Hi,

I looked and couldn't find this answer, so if this question has already been
answered I apologize in advance.

I would like to create a field in my spreadsheet that automatically updates
the date and time upon saving. I found the Now() and Date() functions but I
couldn't find anything that updates upon saving. I don't know a lot about
scripting but I'd be willing to give that a shot if that is what it takes.

Jennifer Marrs

To do this accurately, you'll need a short VBA program. With the workbook
active, press Option-F11. Double click on the thisworkbook icon in the upper
left window. Pull down the upper left drop-down so it says workbook instead
of general. Then copy and paste the following on the line after End Sub:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ThisWorkbook.Sheets("sheet1").Range("a1") = Now
End Sub

You can then delete the empty workbook_open sub. Change the sheet name and
range to wherever you want the date to appear. Also, watch out for line
wraps. The stuff you should paste is only three lines.

 

[Jennifer Marrs]

I got this to work and it is great. So now a second question, is there a way
to put this in a footer instead of a cell?

Thanks in advance!

PS: How does one become a Mac Office MVP? What books would you recommend? I
taught Windows office applications for a while, but now I¹m on the Mac
exclusively at work and would like to know more.

 

[JE McGimpsey]

I got this to work and it is great. So now a second question, is there a way
to put this in a footer instead of a cell?

One way:

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Sheets(1).PageSetup.LeftFooter = _
Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

You can substitute RightFooter or CenterFooter if desired. You can also
assign formats just as through the GUI - take a look at XL/VBA Help
("Formatting Codes for Headers and Footers")

PS: How does one become a Mac Office MVP?

The MVP award is just that, an award, given annually for those that
provide assistance to the community. There are no explicit criteria, nor
any type of certification. See



Generally, most MacOffice MVP awards have been made to regular
contributors to the newsgroups over an extended period.

 

[Jennifer Marrs]

JE,

Thanks for the answer to both questions. I will try the script and see what
I come up with.

: )

Jennifer