Make a macro availiable to all workbooks

[Ernest L. Lippert]

I would like to put the code below somewhere so that it will be activated
every time a worksheet is viewed or printed. I have tried putting it in a
module in my Private.xls workbook but since I don't call it in a procedure,
it doesn't work like I want. The only way it works is if I put it into the
code behind each of the workbooks in use.

I have a particular need to have our bookkeeper the put the path name on
all the worksheets except in those instances where it is sent as a pdf or
xls
file for inclusion in other documents. In those instances the footer needs
to be consciously removed. If I can cajole Excel to use this code, it will
keep our church administrator much happier.

Regards,
Ernie Lippert
------------------------------------------------------
Option Explicit
Sub Workbook_BeforePrint(Cancel As Boolean)
Call ErnieAddPath
End Sub

Sub ErnieAddPath()
Dim Ans As XlYesNoGuess
Ans = MsgBox("Add path to footer?", vbYesNo + vbQuestion, "Tell Me")
If Ans = vbNo Then
ActiveSheet.PageSetup.LeftFooter = ""
Else: ActiveSheet.PageSetup.LeftFooter = "&8" &
LCase(ThisWorkbook.FullName)
End If
End Sub

 

[Dave Peterson]

John Walkenbach has an addin that will add the path to the footer. The user can
run it for any worksheet that they want (a manual effort), but that seems to
make sense to me if you want it sometimes and not other times.

John Walkenbach's addin:
http://j-walk.com/ss/excel/files/addpath.htm

And you can remove the code that adds the footer any time you want.

 

[Ernest L. Lippert]

Dave,
Thanks. I have JW's PUP and it works fine. However, in my particular
application I want the bookeeper to be prompted to make a conscious decision
to foot or not to foot. Following Walkenbach's lead on p. 539 of his Power
Programming with VBA book, I tried the code (revised slightly from my first
version) below as a class module in Personal.xls but I couldn't get it to
work for all workbooks - only the ones into which I manually inserted it. In
the properties pane under Instancing the dropdown box said
2-PublicNotCreatable. Any ideas?

Again, thank you very much for your suggestion.
Regards,
Ernie
p.s. I sent this message first to Dave but it didn't reach him. This time I
am sending it to
"Reply Group" with thge hope that it might work. I am new to newsgroup
operation.

--------------------------------------------------------------------
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 = "&""""&6" & LCase(ThisWorkbook.FullName)
Next sht
End If
End Sub
-------------------------------------------------------------------------

 

[Dave Peterson]

This worked ok for me. I put it all under ThisWorkbook.

Option Explicit
Public WithEvents AppEvents As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set AppEvents = Nothing
End Sub
Private Sub Workbook_Open()
Set AppEvents = Excel.Application
End Sub
Private Sub AppEvents_WorkbookBeforePrint(ByVal wb As Workbook, _
Cancel As Boolean)
Call ErnieAddPath(wb)
End Sub
Private Sub ErnieAddPath(wb As Workbook)
Dim Ans As Long
Dim Sht As Worksheet
Dim myFooter As String

Ans = MsgBox("Add path to footer?", vbYesNo + vbQuestion, "Tell Me")
If Ans = vbNo Then
myFooter = ""
Else
myFooter = "&""""&6" & LCase(wb.FullName)
End If
For Each Sht In wb.Sheets
Sht.PageSetup.LeftFooter = myFooter
Next Sht
End Sub

But I think I wouldn't use personal.xls. I'd create a separate addin:
ErnieAddin.xla and give it to the users and tell them to install it via:
Tools|addins.