XL: Inserting and Formatting Text in Headers and Footers

Article translations Article translations
Article ID: 142136 - View products that this article applies to.
This article was previously published under Q142136
Expand all | Collapse all

On This Page

SUMMARY

In Microsoft Excel, you may want to use a header or footer in a worksheet. The "More Information" section of this article discusses the formatting codes that you can use to automatically insert and format text in a header or footer, and it also contains a sample Microsoft Visual Basic for Applications macro that you can used to add and format text in the header and footer of a worksheet.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Formatting Codes for Headers and Footers

To use multiple lines in a header, use either of the following methods:
  • Use CHR(10) to insert a linefeed character.
  • Use CHR(13) to insert a carriage return character.
Note that you cannot record these characters in a macro.

The following tables contain the format codes that you can use in headers and footers.
   Codes to format text
   --------------------------------------------------------------------

   &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.
   &O              Turns outline printing on or off (Macintosh only).
   &H              Turns shadow printing on or off (Macintosh only).
   &"fontname"     Prints the characters that follow in the specified
                   font.
                   Be sure to include the quotation marks around the
                   font name.
   &nn             Prints the characters that follow in the specified
                   font size. Use a two-digit number to specify a size
                   in points.
				

   Codes to insert specific data
   --------------------------------------------------------------------

   &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 (the "sheet name").
   &P           Prints the page number.
   &P+number    Prints the page number plus number.
   &P-number    Prints the page number minus number.
   &&           Prints a single ampersand.
   &N           Prints the total number of pages in the document.
				

Sample Visual Basic Procedure

Note that the following code samples assume that the list has a header row starting in cell A1 and data starting in A2. Note also that the "~" indicates a step to be performed on each line of the loop, or at a specified time.

To create a sample macro that uses some of the formatting codes:
  1. Open a new workbook.
  2. Insert a module sheet into the workbook. In Microsoft Excel 97, point to Macro on the Tools menu, and click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu. In Microsoft Excel 7.0, point to Macro on the Insert menu and click Module.
  3. Type the following macro on the module sheet:
          Sub Format_Codes()
    
             'The line below will print the words "header text" underlined
             'and in font size 24. Even though the CenterHeader is
             'indicated, the "&L" will force it to the left.
    
             ActiveSheet.PageSetup.CenterHeader = "&L&U&24header text"
    
             'This line of code will format the words, "my text", in the
             'font Arial and use Bold. Notice that each piece is enclosed in
             'quotation marks.
    
             ActiveSheet.PageSetup.RightHeader = "&""arial,bold""my text"
    
             'To get more than one line, concatenate the linefeed or return
             'character into the string.
    
             ActiveSheet.PageSetup.CenterHeader = "First line" & Chr(13) & _
                "Second line"
    
             'The following will put the current date in the left footer,
             'the file name in the center footer and the number of pages and
             'the total number of pages in the right footer.  The last uses
             'simple concatenation to achieve the desired result.
    
             With ActiveSheet.PageSetup
               .LeftFooter = "&D"
               .CenterFooter = "&F"
               .RightFooter = "Page " & "&P" & " of " & "&N"
             End With
    
          End Sub
    						
    NOTE: To type a format code, enclose it in quotation marks. To use the format code for font type, enclose the name in two sets of quotation marks (for example, type ""Arial""). You can also format user-supplied text.
  4. Return to Sheet1 and run the Format_Codes macro created in step 3.
  5. To view the results, click the Print Preview button on the Standard toolbar, or click Print Preview on the File menu.
For additional information, please click the article number below to view the article in the Microsoft Knowledge Base:
141578 XL: How to Show Quotation Marks in Function Results and Macros

REFERENCES

In Microsoft Excel 7.0, for more information about formatting codes, click the Index tab in Microsoft Excel Help, type the following text
headers
and then double-click the selected text to go to the "Formatting Codes for Headers and Footers" topic.

For more information about formatting codes for headers and footers, click Answer Wizard on the Help menu and type:
format header footer

Properties

Article ID: 142136 - Last Review: October 11, 2006 - Revision: 2.3
APPLIES TO
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 5.0c
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 5.0 for Macintosh
  • Microsoft Excel 5.0a for Macintosh
Keywords: 
kbdtacode kbfaq kbhowto kbprogramming kbualink97 KB142136
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com