HI,

I am trying to develop some VBA code in excel 2003 that will
automatically place a specific header and footer on any sheet I print
from excel.

So far I have created the following code in my personal workbook.

Public Sub App_WorkbookBeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = "&D &T"
ActiveSheet.PageSetup.CenterFooter = "&F"
End Sub

It appears to work for that workbook but not others I have open.

Can anyone suggest how I can make this code apply to the excel
application instead of to a particular workbook?

Thanks,

Woody,
Application events. Then check if it is workbook that you wish to process.

http://www.cpearson.com/excel/AppEvent.htm

NickHK

"Woody" wrote in message
ups.com...
HI,

I am trying to develop some VBA code in excel 2003 that will
automatically place a specific header and footer on any sheet I print
from excel.

So far I have created the following code in my personal workbook.

Public Sub App_WorkbookBeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = "&D &T"
ActiveSheet.PageSetup.CenterFooter = "&F"
End Sub

It appears to work for that workbook but not others I have open.

Can anyone suggest how I can make this code apply to the excel
application instead of to a particular workbook?

Thanks,

Woody,

You need application events, and you need to invoke them from a workbook
that is opened whenever Excel is started. An obvious place is Personal.xls.
Add this code

Public WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
With Wb.ActiveSheet.PageSetup
.RightFooter = "&D &T"
.CenterFooter = "&F"
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

"Woody" wrote in message
ups.com...
HI,

I am trying to develop some VBA code in excel 2003 that will
automatically place a specific header and footer on any sheet I print
from excel.

So far I have created the following code in my personal workbook.

Public Sub App_WorkbookBeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = "&D &T"
ActiveSheet.PageSetup.CenterFooter = "&F"
End Sub

It appears to work for that workbook but not others I have open.

Can anyone suggest how I can make this code apply to the excel
application instead of to a particular workbook?

Thanks,