Headers and footers

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
913
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top