date modified in footer

maryj · 07-27-2005, 02:05 PM

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

JE McGimpsey · 07-27-2005, 05:05 PM

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 <[email protected]>,
"maryj" <[email protected]> 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?

Nick Hodge · 07-27-2005, 05:05 PM

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
[email protected]HIS

"maryj" <[email protected]> wrote in message
news:[email protected]...
> 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

maryj · 07-27-2005, 05:05 PM

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
> [email protected]HIS
>
>
> "maryj" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

John_Ostar · 10-10-2005, 06:05 PM

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
> > [email protected]HIS
> >
> >
> > "maryj" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

John_Ostar · 10-10-2005, 06:05 PM

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
> > > [email protected]HIS
> > >
> > >
> > > "maryj" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >