|
|
|
|
|
|
|
|
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:
|
- Copy the above code.
- Open any workbook.
- Press Alt + F11 to open the Visual Basic Editor (VBE).
- 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]
- Select an ?ThisWorkBook? code module for the target worksheet
- Paste the code into the right-hand code window.
- Close the VBE, save the file if desired.
- See ?Test The Code? below
|
|
Test the code:
|
- Open the example
- The example contains a single sheet with some cells of numbers (so there is something to preview / print).
- The procedures Workbook_BeforePrint, xlStdHeader, and xlStdFooter are already loaded in the ThisWorkBook code module.
- Click on print preview to see results.
|
|
Sample File:
|
xlStdHeaderFooter.zip 14.48KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 173 times.
|
|
|