Global Setting For All Workbooks - Filename In Footer

I would like to create an Add-In which will place the filename into the
footer. I curently use a macro to do this, but I'd like to have this apply to
all files I open (unless I 'turn off' the Add-In).

The macro I currently use is below ....

Sub PrintWithFileNameInFooter()
With ActiveSheet.PageSetup
.LeftFooter = "&8" & _
LCase(ActiveWorkbook.FullName) & " " & ActiveSheet.Name
If .RightFooter = "" Then .RightFooter = "Page &P of &N"
End With
End Sub


Thanks - -TB- -

I think your best bet is a Class Module in your Personal.xls
[All the names in the following example are just that, examples.]
To do this you would create a class module in the VBE of your Personal.xls
(insert / class module).
1) Name the module CLASS_PrintFooter
2) At the top of the class module put…

Option Explicit

Public WithEvents App_MyPrintFooter As Application

Private Sub App_MyPrintFooter _Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "&8" & _
LCase(ActiveWorkbook.FullName) & " " & ActiveSheet.Name
If .RightFooter = "" Then .RightFooter = "Page &P of &N"
End With
End Sub

3) At the top of the 'ThisWorkbook' module of the Personal.xls put...

Option Explicit

Dim AppClass As New CLASS_PrintFooter

Private Sub Workbook_Open()
Set AppClass.App_MyPrintFooter = Application
End Sub


This program is untried.

HTH,
Gary Brown


"TOMB" wrote:

> I would like to create an Add-In which will place the filename into the
> footer. I curently use a macro to do this, but I'd like to have this apply to
> all files I open (unless I 'turn off' the Add-In).
>
> The macro I currently use is below ....
>
> Sub PrintWithFileNameInFooter()
> With ActiveSheet.PageSetup
> .LeftFooter = "&8" & _
> LCase(ActiveWorkbook.FullName) & " " & ActiveSheet.Name
> If .RightFooter = "" Then .RightFooter = "Page &P of &N"
> End With
> End Sub
>
>
> Thanks - -TB- -

Take a look here for one way:

http://www.mcgimpsey.com/macoffice/e...infooter2.html


In article <[email protected]>,
"TOMB" <[email protected]> wrote:

> I would like to create an Add-In which will place the filename into the
> footer. I curently use a macro to do this, but I'd like to have this apply to
> all files I open (unless I 'turn off' the Add-In).
>
> The macro I currently use is below ....
>
> Sub PrintWithFileNameInFooter()
> With ActiveSheet.PageSetup
> .LeftFooter = "&8" & _
> LCase(ActiveWorkbook.FullName) & " " & ActiveSheet.Name
> If .RightFooter = "" Then .RightFooter = "Page &P of &N"
> End With
> End Sub
>
>
> Thanks - -TB- -

Private Sub App_MyPrintFooter _WorkbookBeforePrint(Cancel As Boolean)
NOT
Private Sub App_MyPrintFooter _Workbook_BeforePrint(Cancel As Boolean)

Sorry,
Gary Brown


"Gary Brown" wrote:

> I think your best bet is a Class Module in your Personal.xls
> [All the names in the following example are just that, examples.]
> To do this you would create a class module in the VBE of your Personal.xls
> (insert / class module).
> 1) Name the module CLASS_PrintFooter
> 2) At the top of the class module put…
>
> Option Explicit
>
> Public WithEvents App_MyPrintFooter As Application
>
> Private Sub App_MyPrintFooter _Workbook_BeforePrint(Cancel As Boolean)
> With ActiveSheet.PageSetup
> .LeftFooter = "&8" & _
> LCase(ActiveWorkbook.FullName) & " " & ActiveSheet.Name
> If .RightFooter = "" Then .RightFooter = "Page &P of &N"
> End With
> End Sub
>
> 3) At the top of the 'ThisWorkbook' module of the Personal.xls put...
>
> Option Explicit
>
> Dim AppClass As New CLASS_PrintFooter
>
> Private Sub Workbook_Open()
> Set AppClass.App_MyPrintFooter = Application
> End Sub
>
>
> This program is untried.
>
> HTH,
> Gary Brown
>
>
> "TOMB" wrote:
>
> > I would like to create an Add-In which will place the filename into the
> > footer. I curently use a macro to do this, but I'd like to have this apply to
> > all files I open (unless I 'turn off' the Add-In).
> >
> > The macro I currently use is below ....
> >
> > Sub PrintWithFileNameInFooter()
> > With ActiveSheet.PageSetup
> > .LeftFooter = "&8" & _
> > LCase(ActiveWorkbook.FullName) & " " & ActiveSheet.Name
> > If .RightFooter = "" Then .RightFooter = "Page &P of &N"
> > End With
> > End Sub
> >
> >
> > Thanks - -TB- -