Microsoft Office Tutorials and References
In Depth Information
Getting Acquainted with Workbook-Level Events
Updating a header or footer
Excel’s page header and footer options are very flexible, but these options don’t include a common
request: the ability to print the contents of a specific cell in the header or footer. The Workbook_
BeforePrint event provides a way to display the current contents of a cell in the header or
footer when the workbook is printed. The following code updates each sheet’s left footer whenever
the workbook is printed or previewed. Specifically, it inserts the contents of cell A1 on Sheet1:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sht As Object
For Each sht In ThisWorkbook.Sheets
sht.PageSetup.LeftFooter = _
Worksheets(“Sheet1”).Range(“A1”)
Next sht
End Sub
This procedure loops through each sheet in the workbook and sets the LeftFooter property of
the PageSetup object to the value in cell A1 on Sheet1.
Hiding columns before printing
The example that follows uses a Workbook_BeforePrint procedure to hide columns B:D in
Sheet1 before printing or previewing.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
‘Hide columns B:D on Sheet1 before printing
Worksheets(“Sheet1”).Range(“B:D”).EntireColumn.Hidden = True
End Sub
Ideally, you would want to unhide the columns after printing has occurred. It would be nice if Excel
provided an AfterPrint event, but that event doesn’t exist. However, there is a way to unhide
the columns automatically. The modified procedure that follows schedules an OnTime event,
which calls a procedure named UnhideColumns five seconds after printing or previewing.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
‘Hide columns B:D on Sheet1 before printing
Worksheets(“Sheet1”).Range(“B:D”).EntireColumn.Hidden = True
Application.OnTime Now()+ TimeValue(“0:00:05”), “UnhideColumns”
End Sub
The UnhideColumns procedure goes in a standard VBA module.
Sub UnhideColumns()
Worksheets(“Sheet1”).Range(“B:D”).EntireColumn.Hidden = False
End Sub
 
Search JabSto ::




Custom Search