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?
--
maryj

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?



In article ,
"maryj" wrote:

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?

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
HIS


"maryj" wrote in message
...
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?
--
maryj

Thanks Nick! That works great!
--
maryj


"Nick Hodge" wrote:

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
HIS


"maryj" wrote in message
...
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?
--
maryj

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

"maryj" wrote:

Thanks Nick! That works great!
--
maryj


"Nick Hodge" wrote:

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
HIS


"maryj" wrote in message
...
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?
--
maryj

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


"John_Ostar" wrote:

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

"maryj" wrote:

Thanks Nick! That works great!
--
maryj


"Nick Hodge" wrote:

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
HIS


"maryj" wrote in message
...
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?
--
maryj