Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Headers and footers

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are there any undocumented page setup codes in addition to the &[Tab], &[Date],&[Time], &[File], &[Page] and &[Pages] for use in headers and footers?

    I often use Page &[Page]& of &[Pages] as a default page set-up. Is there a way to code it automatically so that if there is only one page either there is no page number or just "1" rather than "1 of 1"? This probably requires VBA.

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi inarbeth,

    Here is a complete list of the available header/footer format codes:

    Format code Description
    &L Left aligns the characters that follow.
    &C Centers the characters that follow.
    &R Right aligns the characters that follow.
    &E Turns double-underline printing on or off.
    &X Turns superscript printing on or off.
    &Y Turns subscript printing on or off.
    &B Turns bold printing on or off.
    &I Turns italic printing on or off.
    &U Turns underline printing on or off.
    &S Turns strikethrough printing on or off.
    &D Prints the current date.
    &T Prints the current time.
    &F Prints the name of the document.
    &A Prints the name of the workbook tab.
    &P Prints the page number.
    &P+number Prints the page number plus the specified number.
    &P-number Prints the page number minus the specified number.
    && Prints a single ampersand.
    & "fontname" Prints the characters that follow in the specified font. Be sure to include the double quotation marks.
    &nn Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.
    &N Prints the total number of pages in the document.

    Unfortulately, I believe that what you want to do cannot be done via these codes. It should not be hard to do it via VBA, however. You could even put the code in the Before_Print event so that it automatically adjusts the headers/footers appropriately whenever you print the document.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. Anyone out there done any VBA for headers and footers? Even if not directly in reply to my query, I would be interested to see what's possible.

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. Anyone out there done any VBA for headers and footers? Even if not directly in reply to my query, I would be interested to see what's possible.

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again inarbeth,

    Here is an example of what I believe you are looking for. The following workbook Before_Print event code will make the center footer blank if only one page, but will give a center footer "Page P of N" form if the sheet has more than one page.

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim Sh As Object
    For Each Sh In ActiveWindow.SelectedSheets
    If Sh.HPageBreaks.Count + Sh.VPageBreaks.Count = 0 Then
    Sh.PageSetup.CenterFooter = ""
    Else
    Sh.PageSetup.CenterFooter = "Page &P of &N"
    End If
    Next Sh
    End Sub

    To install this macro, right click on the Excel icon at the left end of the Worksheet Menu bar, select View Code, and paste this code into the VBE code pane.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Damon
    Sorry for the delay in replying. I forgot to check back. I cannot get the code to work. On print preview no page numbering appears. We have network printers. Might that affect matters?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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