Headers and footers

[inarbeth]

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.

[Damon Ostrander]

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.

[inarbeth]

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.

[inarbeth]

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.

[Damon Ostrander]

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.

[inarbeth]

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?