Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FOOTER'S DATE (97)

    HOW CAN I INSERT THE TODAY'S DATE ON THE FOOTER AND LET IT STAY UNCHANGED WHEN I PRINT THE DOC ON OTHER DAY.
    THANK YOU FOR ANY HELP.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FOOTER'S DATE (97)

    Select Page Setup from the File menu. Click on the Header/Footer Tab in the Dialog Box. Click on the "Custom Footer" button. In the Footer dialog box, click in the section of the footer where you want the date to appear (Left, Center, or Right) and then type in the date you want displayed in the footer. Click OK until all dialog boxes are closed.

    You could also use a VBA macro like the one below to set todays date into the footer:

    <pre>Public Sub SetFooter()
    ActiveWorkbook.Worksheets("Sheet1").PageSetup.Cent erFooter = Format(Date, "dd-MMM-YYYY")
    End Sub
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Location
    California, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FOOTER'S DATE (97)

    Hey joe,

    Did LC's suggestion work for you? I feel your pain! :-) My only solution was to stop using any of the "auto-date" footer entries and just use plain text that I have to change when I revise the document. All the other things I tried change when I open or print the darn files. Of course, then it is up to me to manually change the date when I revise a doc, but I'm getting used to it.

    Hope this helps! - Thomas

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FOOTER'S DATE (97)

    The first question is whether you want to do this to every (or many) documents, or just to one? I had a situation where I was printing large financial models reflecting different assumptions, and it was convenient to highlight the assumptions in the footers.

    I wrote the following VBA routine to insert specified text in the footers, and inserted a button on the first page of the model to run the routine:

    <pre>Sub FooterText()
    Dim Foot1 As String
    Dim Foot2 As String
    Dim Foot3 As String
    Dim Foot4 As String
    Dim Worksht As Object

    Foot1 = Range("Footer1").Value
    Foot2 = Range("Footer2").Value
    Foot3 = Range("Footer3").Value
    Foot4 = Range("Footer4").Value
    For Each Worksht In Worksheets
    Worksht.Activate
    With ActiveSheet.PageSetup
    .LeftFooter = Foot1 & Chr(13) & Foot2 & Chr(13) & "&F &A"
    .CenterFooter = Format(Now, "Mmmm d 'yy") & " " & "&T" _
    & Chr(13) & "page " & "&P" & " of " & "&N"
    .RightFooter = Foot3 & Chr(13) & Foot4
    End With
    Next Worksht
    End Sub
    </pre>


    The named ranges Footer1 to Footer 4 contained formulas to concatenate text as values, for example:

    Cell C56 is the named range Footer1:
    Cell A56 contains "Maximum Assets eligible for Tax Depreciation: " (<--note the space after the colon)
    Cell B56 contains a formula picking up the calculation from elsewhere on the S/sheet - say $150,256,223
    Cell C56 contains "=CONCATENATE(A56, text(B56,"$#,##0.0,,"), " Million")

    This produces a nice text string "Maximum Assets eligible for Tax Depreciation: $150.3 Million" - which is then set down in the first line of the left footer. Similar concatenations produce other formated text in the Footer2-Footer4 ranges

    As you can see, the center footer contains the date and time that the footers were updated - usually immediately before the model was printed. It is slow (I could speed it up by setting screen updating on and off, but it was really a one-off), but MUCH faster than going and amending the footers in every sheet (about twelve) every time we wanted to try a different set of assumptions (about eight times a day)- which in turn was preferable to not knowing which assumptions lead to the result on each page and constantly having to refer back to the assumptions page....

    If you want to be able to do this for a number of s/sheets, include something like:

    <pre>Sub Datestamp()
    ActiveSheet.PageSetup.CenterFooter = Format(Now, "Mmmm d 'yy")
    End Sub
    </pre>


    In your personal.xls file - that way it will always be available to insert todays date in the centre footer. This will be inserted as a string - it won't change subsequently.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: FOOTER'S DATE (97)

    Try something like this, It gets the LAST saved date. If you do not save your worksheet everyday the date will NOT change.

    Steve

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.LeftFooter = _
    "Last Saved: " & _
    Format(FileDateTime(ThisWorkbook.FullName),"mmmm d, yyyy")
    End Sub

  6. #6
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: FOOTER'S DATE (97)

    If you would like to show:
    1. Date the sheet/book was created and
    2. Date the sheet/book was printed
    In one footer pane type "Created xx/xx/xxxx" so it is text
    In another footer pane insert the date from the footer tools, precede this with the words "revised" or "Printed" <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: FOOTER'S DATE (97)

    Creation date and Printed date
    Steve

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.LeftFooter = _
    "Created: " & _
    format(ThisWorkbook.BuiltinDocumentProperties("Cre ation Date"),"mmmm d, yyyy)

    ActiveSheet.PageSetup.rightFooter = _
    "Printed: " & _
    format (Now, "mmmm d, yyyy")
    End Sub

    Another way to enter the saved date:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ActiveSheet.PageSetup.centerFooter = _
    "Saved: " & _
    format (Now, "mmmm d, yyyy")
    End Sub

    Steve

  8. #8
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: FOOTER'S DATE (97)

    Thanks Steve for that information, and for taking the time to help a VBA tyro
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  9. #9
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FOOTER'S DATE (97)

    To get around my problem, in my vba, I put the =today() in b5,copy b5, and pastespecial only value to b5
    and use the code below. After this code, clear the b5. In this way, I can permanently save the created date in my footer, no matter I will or not save the file later.
    any other idea?

    ActiveSheet.PageSetup.Leftfooter = _
    Format(Worksheets("Sheet2").Range("B5").Value)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •