Excel

standardized Excel headers and footers

Ease of Use

Easy

Version tested with

2000 

Submitted by:

MWE

Description:

xlStdHeader and xlStdFooter build "standard" headers and footers for printing. 

Discussion:

Excel offers many header and footer options. Over time, many users develop their own "standard" header and footer. xlStdHeader and xlStdFooter offer one approach to such standards. They are called from the Workbook_BeforePrint proc in the "ThisWorkbook" code module. xlStdFooter places specific information in specific footer sections: file name and sheet name in left footer; date printed and time printed in center footer; and, page # and # of pages in right footer. This is one person's standard and may not be what others want, but the approach is pretty useful. xlStdHeader is provided for consistency of approach. 

Code:

instructions for use

			

Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) ' ' add code placed in this proc is executed before a Print ' ' results ARE shown during PrintPreview ' ' Call xlStdHeader Call xlStdFooter Cancel = False End Sub Sub xlStdFooter() ' '**************************************************************************************** ' Title xlStdFooter ' Target Application: MS Excel ' Function: builds standard Excel footer ' a call to this subroutine is added to the Workbook_BeforePrint proc ' in the "ThisWorkbook" code module ' '**************************************************************************************** ' ' ActiveSheet.PageSetup.LeftFooter = _ "File: " & ActiveWorkbook.Name & vbLf & _ "Tab: " & ActiveSheet.Name ActiveSheet.PageSetup.CenterFooter = _ "Date Printed: " & Format(Date, "dd-mmm-yyyy") & vbLf & _ "Time Printed: " & Format(Time, "hhmm") & " hrs" ActiveSheet.PageSetup.RightFooter = _ "Page #: " & "&P" & vbLf & _ "Total Pages: " + "&N" End Sub Sub xlStdHeader() ' '**************************************************************************************** ' Title xlStdHeader ' Target Application: MS Excel ' Function: builds standard Excel header ' a call to this subroutine is added to the Workbook_BeforePrint proc ' in the "ThisWorkbook" code module ' '**************************************************************************************** ' ' ActiveSheet.PageSetup.LeftHeader = _ "left header stuff" ActiveSheet.PageSetup.CenterHeader = _ "center header stuff" ActiveSheet.PageSetup.RightHeader = _ "right header stuff" End Sub

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
  5. Select an ?ThisWorkBook? code module for the target worksheet
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
  8. See ?Test The Code? below
 

Test the code:

  1. Open the example
  2. The example contains a single sheet with some cells of numbers (so there is something to preview / print).
  3. The procedures Workbook_BeforePrint, xlStdHeader, and xlStdFooter are already loaded in the ThisWorkBook code module.
  4. Click on print preview to see results.
 

Sample File:

xlStdHeaderFooter.zip 14.48KB 

Approved by mdmackillop


This entry has been viewed 284 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express