In my particular application I want our bookkeeper to be prompted to make a
conscious decision to add a footer or not. Following Walkenbach's lead on
p. 539 of his Power
Programming with VBA book, I tried the code below as a class module in
Personal.xls but I couldn't get it to work for all workbooks. It only works
if I manually insert the code in each workbook. In the properties pane for
Personal.xls under Instancing the dropdown box said "2-PublicNotCreatable".
Any ideas about what is wrong? I tried the first line of code with
Excel.Application or Application with no luck.

Do I have to make this an Add-In?

Regards,
Ernie

--------------------------------------------------------------------
Public WithEvents AppEvents As Excel.Application
Private Sub AppEvents_Workbook_BeforePrint(Cancel As Boolean)
Call ErnieAddPath
End Sub
Private Sub ErnieAddPath()
Ans = MsgBox("Add path to footer?", vbYesNo + vbQuestion, "Tell Me")
If Ans = vbNo Then
ActiveSheet.PageSetup.LeftFooter = ""
Else: For Each sht In ThisWorkbook.Sheets
sht.PageSetup.LeftFooter = "&8" & LCase(ThisWorkbook.FullName)
Next sht
End If
End Sub

You have a reply to your earlier post.

"Ernest L. Lippert" wrote:

In my particular application I want our bookkeeper to be prompted to make a
conscious decision to add a footer or not. Following Walkenbach's lead on
p. 539 of his Power
Programming with VBA book, I tried the code below as a class module in
Personal.xls but I couldn't get it to work for all workbooks. It only works
if I manually insert the code in each workbook. In the properties pane for
Personal.xls under Instancing the dropdown box said "2-PublicNotCreatable".
Any ideas about what is wrong? I tried the first line of code with
Excel.Application or Application with no luck.

Do I have to make this an Add-In?

Regards,
Ernie

--------------------------------------------------------------------
Public WithEvents AppEvents As Excel.Application
Private Sub AppEvents_Workbook_BeforePrint(Cancel As Boolean)
Call ErnieAddPath
End Sub
Private Sub ErnieAddPath()
Ans = MsgBox("Add path to footer?", vbYesNo + vbQuestion, "Tell Me")
If Ans = vbNo Then
ActiveSheet.PageSetup.LeftFooter = ""
Else: For Each sht In ThisWorkbook.Sheets
sht.PageSetup.LeftFooter = "&8" & LCase(ThisWorkbook.FullName)
Next sht
End If
End Sub

--

Dave Peterson