Apply a default header / footer using Custom Views

 2004-06-07    Worksheets    0    74

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.


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.