Last Saved Date in Footer

  • Thread starter Thread starter Marcy
  • Start date Start date
M

Marcy

I found within this newsgroup community a post for saving
last saved date to a cell within the worksheet but I
would like to know if this can be done for the worksheet
footer.

And, if so, can you please tell me how I can write it so
that it will be available in ALL (or many) of my
workbooks? If you tell me that I need to UNHIDE my
personal.xls, it won't let me!!

Thanks..as usual....
 
One way using something you probably saw put the date and time in a cell,
then using that cell for a footer


Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As
Boolean)
With Worksheets("Sheet1").Range("IV1")
.Value = Now()
End With
End Sub

Assume you save the date and time in IV1 (where it is not visible for most
occasions)
then you use something like this for printing

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("Sheet1")
.PageSetup.RightFooter = .Range("IV1").Text
End With
End Sub



You can't unhide the personal.xls if you don't have one, you have to create
a personal.xls.
Do tools>macro>record new macro. Then from the dropdown select store macro
in personal macro workbook
Click OK and immediately stop the recording. Press Alt + F11 and select the
personal.xls module in the project pane
(double click it) to the left. Delete all the text and paste in whatever
macros you want to be accessible..
When you close excel later you'll be prompted to save the personal.xls,do
that..
 
I notices some line wrapping when I saw the message, note that

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As
Boolean)

is supposed to be one line
 
Marcy,

Here's a solution that doesn't need to save to the worksheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
.PageSetup.RightFooter = "Document last saved on " & _
Format(ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"), _
"dd mmm yyyy")
End With
End Sub

However, it is no use putting this or Peo's solution in Personal.xls as it
is not a macro, but event code. IMO the best thing to do is put it in the
template spreadsheet, that is create a new spreadsheet, add the code, save
it as a template file called Book.xlt and save to the XLStart directory.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi, my friend, Peo--

Thank you for the code but please forgive my ignorance.
I tried entering this into the VBAProject (personal.xls)
by inserting a module (module2)but when I return to the
spreadsheet I cannot access it in any way. Can you
please tell me how I can do this (or can I have this
assigned to a toolbar button so I can add this feature to
all workbooks?)

I must be missing a step because I couldn't see anything
after I entered this in the module when I next went to
macros to assign a keyboard shortcut or a button for the
toolbar.

Please teach me what step I am missing or if I am putting
it in the wrong window?

Thanks, my dear Peo!
 
Thaks, Bob-

You answered my post before I posted! But, how can I make it available (like a toolbar button or some such) for all open workbooks

Thanks..
 
Ok..I created a new workbook and sure enough, the last saved date was in the footer when I selected print preview.

Now...How can I make it do this on a previously created workbook? I have opened it; saved it; previewed and the footer is BLANK. What am I missing?

Please don't leave me hanging....I feel sooo close!
 
Marcy,

Sorry but you will have to add that code to any previously entered workbook,
in the ThisWorkbok code module.

My Book.xlt solution will only apply when you do a New.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Marcy said:
Ok..I created a new workbook and sure enough, the last saved date was in
Click to expand...
the footer when I selected print preview.
Now...How can I make it do this on a previously created workbook? I have
Click to expand...
opened it; saved it; previewed and the footer is BLANK. What am I missing?
 
No problem, Bob. At least I know it wasn't something I did wrong, for a change!

Thanks for the speedy reply!
 
Back
Top