date modified in footer

[maryj]

In another post Jason Morin provided this code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim LastDate As String
Dim wbProp As String

wbProp = "last save time"
LastDate = ActiveWorkbook.BuiltinDocumentProperties(wbProp)

ActiveSheet.PageSetup.CenterHeader = LastDate

End Sub

This works great in the active workbook. How can this code be made available
to be used when needed in other workbooks?

 

[JE McGimpsey]

What do you mean by "when needed"?

This is an event macro that will fire each time a workbook is printed.
Do you want every workbook to be updated before print, every new
workbook? or just some?

 

[Nick Hodge]

Mary

You could put this into a standard module in personal.xls and attach a
button to activate it

Sub AddPropToFooter

Dim LastDate As String
Dim wbProp As String

wbProp = "last save time"
LastDate = ActiveWorkbook.BuiltinDocumentProperties(wbProp)

ActiveSheet.PageSetup.CenterHeader = LastDate

End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[maryj]

Thanks Nick! That works great!
--
maryj

Mary

You could put this into a standard module in personal.xls and attach a
button to activate it

Sub AddPropToFooter

Dim LastDate As String
Dim wbProp As String

wbProp = "last save time"
LastDate = ActiveWorkbook.BuiltinDocumentProperties(wbProp)

ActiveSheet.PageSetup.CenterHeader = LastDate

End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[John_Ostar]

Mary,

Here is what I use. Feel free to use it. Note it effects ALL sheets in the
workbook including charts. The comments include installation instructions. As
far as starting new workbooks, I keep a blank workbook with the VBA code and
some other custom header and footer stuff I need. I just open the blank and
use Save-As to not change the blank. IMHO it's better than having a seperate
module file and having to remember to click a button.

Good luck,

JohnO

' Author: John Ostar
' Last Modified: 10/10/2005
'Description:
'This VBA code will put the Workbook's Last Modified Date and Time into the
' printed page footer of all sheets, including charts, of the open
workbook.
' It is needed because MS-Excel does not have a field similar to
MS-Word's
' LastSaveDate that can just be easily added to a custom footer.
' This code will run everytime the workbook is saved.
'Installation: Open the desired Workbook. Click Tools->Macro->Visual Basic
Editor.
' Or press ALT-F11
' In Project Explorer, double click on ThisWorkbook and paste this code
into it.
' Close the Visual Basic Editor and Save the Workbook.
'Note: It will be necessary to set Tools->Macro->Security to Low or Medium

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Get the active workbook's last modified date property.
dtMyLastSaveDate = ActiveWorkbook.BuiltinDocumentProperties("Last Save
Time")

'Put value into center footer of every sheet in the workbook
For Each wsheet In Sheets
'the default date format is m/d/yy h:m:s AM/PM
'alternate format is m/d/yy h:mm am/pm
dtMyLastSaveDate = Format(dtMyLastSaveDate, "m/d/yy h:m am/pm")
wsheet.PageSetup.CenterFooter = "Last Modified: " & dtMyLastSaveDate
Next wsheet
End Sub

 

[John_Ostar]

Opps. One error in my comments left over from an earlier attempt.
'This code will run everytime the workbook is PRINTED (not saved).
Sorry.