Friday, January 21, 2011

Adding Date and Time Last Modified to footer of an Excel worksheet

Here is a quick way to add Date and Time Modified (i.e. Last Saved) to your excel sheet to make sure no replication conflict exist or track latest updated version:
Please note (this is not a Data() function)

I. Particular Worksheet
a. Minimize inner window (i.e. spreadsheet you are working on) within the main Excel window.
b. You should not see the Excel Icon on the top of the worksheet (next to the title)
c. Right Click -> Select "View Code"
d. Past this code in the window that opened up
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
End Sub
d. Click on ALT + Q to close window.
f. See date Last Saved in the footer in the print view after you save the file

II. Entire Workbook - applied across all worksheets in the document
a-c. Same as above
d. In the left top menu select ThisWorkBook
e. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each Sheet In ThisWorkbook.Sheets
Sheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
Next Sheet
End Sub
f. Click ALT+Q to close window

No comments:

Post a Comment