Solved: add headers to print then set page setup settings back.

[Danny]

I am wanting to add a header/footer to every excel document i print with the file name, date, time etc. But the some of the files may already have existing Page Setup options.
So i am wanting to have a macro in my personal.xls book that adds (or overwrites) the current header/footer options for my print out and then sets the header/footer options back the way they were.

My options would look something like this:

[vba]
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Set wb = ActiveWorkbook
Dim sh As Worksheet
Dim sUserName As String
sUserName = Environ$("username")

For Each sh In wb
With sh.PageSetup
.LeftHeader = "&D&T"
.center
.RightHeader = sUserName
.LeftFooter = "&Z&F. TAB &A"
.RightFooter = "&P of &N"
End With
Next sh
End Sub
[/vba]

[GTO]

Greetings Danny,

I have never used a Personal.xls, but was thinking that maybe a small add-in might be worth consideration.

In a new workbook:

In ThisWorkbook Module:

[vba]Option Explicit
Dim X As New clsHeader

Private Sub Workbook_Open()
Set X.XLApp = Application
End Sub
[/vba]

• Then create a new class module
• Name this: clsHeader
• In clsHeader:

[vba]Option Explicit

Public WithEvents XLApp As Application

Dim bolInProcess As Boolean
Dim bolIsSaved As Boolean

Private Sub XLApp_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Dim wksActive As Worksheet
Dim HeaderLeft
Dim HeaderRight
Dim FooterLeft
Dim FooterRight

If Not bolInProcess Then

bolInProcess = True
bolIsSaved = Wb.Saved

Cancel = True

Set wksActive = ActiveSheet

With wksActive.PageSetup
HeaderLeft = .LeftHeader
HeaderRight = .RightHeader
FooterLeft = .LeftFooter
FooterRight = .RightFooter

.LeftHeader = "&D&T"
.RightHeader = Environ("username")
.LeftFooter = "&Z&F &A"
.RightFooter = "&P of &N"

wksActive.PrintOut

.LeftHeader = HeaderLeft
.RightHeader = HeaderRight
.LeftFooter = FooterLeft
.RightFooter = FooterRight

bolInProcess = False
Wb.Saved = bolIsSaved
End With

End If

End Sub
[/vba]

Then saveas an add-in.

Please try this on only junk/temp wb's first. I did test a bit, but would haet to have something goober up your other wb's.

Also, please note that this will only work for printing the active sheet. It will not work for printing out multiple selected sheets.

Hopefully anyone spotting any horrors in my code will jump in...

Mark