|
|
|
|
|
|
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)
Call xlStdHeader
Call xlStdFooter
Cancel = False
End Sub
Sub xlStdFooter()
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()
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 284 times.
|
|