XL2000: Inserting and Formatting Text in Headers and Footers

Formatting Codes for Headers and Footers

• Use CHR(10) to insert a linefeed character.
• Use CHR(13) to insert a carriage return character.

   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.
   &"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

1. Open a new workbook.
2. Insert a module sheet into the workbook. Point to Macro on the Tools menu, and click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu.
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.