Revision Date in Footer

[Guest]

I need to put a "Devised" and "Revised" date field in my footer. The "Devised Date" I can manually key in, but how do I get the "Revised Date" to update every time the file is revised? Is there a way

Many thanks
Brian

 

[Frank Kabel]

Hi
you need VBA for this. Put the following code in your
workbook module (not in a standard module):
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.LeftHeader = Me.BuiltinDocumentProperties _
("last save time")
End With
Next wkSht
End Sub

 

[Guest]

Frank - this worked great for getting the last date saved into the Footer. However, it has overwritten all of the other text that I had in the Right Footer area. How do I get both to appear

Thanks
Bria

----- Frank Kabel wrote: ----

H
you need VBA for this. Put the following code in you
workbook module (not in a standard module)
Private Sub Workbook_BeforePrint(Cancel As Boolean
Dim wkSht As Workshee
For Each wkSht In Me.Worksheet
With wkSht.PageSetu
.LeftHeader = Me.BuiltinDocumentProperties
("last save time"
End Wit
Next wkSh
End Su

-
Regard
Frank Kabe
Frankfurt, German


Brian wrote

 

[Peo Sjoblom]

You have to put in all the info in the macro and remove the data you put in
pagesetup or change either
that header or the macro header to either right or center but if you want
all info in the left header
you need to put it all in the macro

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Frank - this worked great for getting the last date saved into the Footer.

However, it has overwritten all of the other text that I had in the Right
Footer area. How do I get both to appear?

 

[Dave Peterson]

I agree with Peo that it would be simpler to dedicate a portion of the
header/footer for this, but if your consistent with your layout of your footer,
you could look for the time/date and replace it each time the code runs.

For instance, if I put:
This is my file and you shouldn't touch it. Last Saved: 06/02/2004 18:24:33

as my footer, I could strip out the " last updated: mm/dd/yyyy hh:mm:ss" and add
them back with the most recent time/date of save.

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim wkSht As Worksheet
Dim myRightFooter As String
Dim myRFPattern As String
Dim myRFPrefix As String

myRFPrefix = " Last Saved: "
myRFPattern = myRFPrefix & "##/##/#### ##:##:##"

For Each wkSht In Me.Worksheets
With wkSht.PageSetup
myRightFooter = .RightFooter
If myRightFooter Like "*" & myRFPattern Then
myRightFooter = Left(myRightFooter, _
Len(myRightFooter) - Len(myRFPattern))
End If
.RightFooter = myRightFooter & myRFPrefix _
& Format(Me.BuiltinDocumentProperties("last save time"), _
"mm/dd/yyyy hh:mm:ss")
End With
Next wkSht

End Sub