Apply a default header/footer using Custom Views

The macros below can be used to apply a default header/footer to a worksheet before it is printed. Any changes the user have made to the headers/footers will be overwritten and lost.
This might be useful since the headers/footers of a worksheet is not protected against changes when you apply the worksheet/workbook protection.

Create your workbook and set the headers/footers you want to include when the worksheets are printed.
Copy and paste the macros below into a normal module sheet:

Sub StoreDefaultViews()
Dim strCurSheet As String, ws As Worksheet, strView As String
    If ActiveWorkbook Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    strCurSheet = ActiveSheet.Name
    With ActiveWorkbook
        For Each ws In .Worksheets
            ws.Activate
            strView = "DefaultView_" & ws.Name
            On Error Resume Next
            .CustomViews(strView).Delete
            .CustomViews.Add strView, True, True
            On Error GoTo 0
        Next ws
    End With
    Sheets(strCurSheet).Activate
End Sub

Sub ApplyDefaultView()
    If ActiveWorkbook Is Nothing Then Exit Sub
    With ActiveSheet
        On Error Resume Next
        .Parent.CustomViews("DefaultView_" & .Name).Show
        On Error GoTo 0
    End With
End Sub

Copy and paste the macro below into the ThisWorkbook module sheet:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ApplyDefaultView
End Sub

In Excel you can now press Alt+F8 and run the macro StoreDefaultViews to save the header/footer settings (and more).
Every time the workbook/worksheets are printed (or previewed), the default view (included the headers/footers) will be applied.

Document last updated 2004-06-07 09:38:59 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-05-31 12:11:28 CET):

Re: Is there a word version
No, Word documents does not support the CustomViews property.

RG wrote (2006-05-30 12:23:03 CET):

Is there a word version
Hello i need a MS word Version for this article.