Make adding a footer optional with message box

[Ernest L. Lippert]

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]

You have a reply to your earlier post.

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

 

[Ernest L. Lippert]

Dave,
Thanks. I did receive your reply; however, it did not fully answer my
question so I tried another post in a different way. Compound that with the
fact that this is my first use of a user group and I am feeling my way
around on the mechanics of the process. Do I reply to you at your address
( replies to you at ...XSPAM.net came back undeliverable) or do I reply to
the newsgroup? This time I will try to send it to the newsgroup. I
appreciate your advice on the proper procedure.
Regards,
Ernie
(e-mail address removed)
********************The error message received is*******************
Your message has encountered delivery problems
to the following recipient(s):

(e-mail address removed)
(Was addressed to (e-mail address removed))
Delivery failed


Can't interpret host name, or non-existant host (WS-Error: 11001)
**************************************************************

 

[Dave Peterson]

You reply in the newsgroups. That way anyone can add to the discussion.

I meant that I replied earlier today. Did you see that post?

 

[Ernest L. Lippert]

Dave,
I saw your reply on 1/31/06. I think there was an earlier one that referred
me to Walkenbach. That message got deleted by mistake. I did reply to it
directly to your email address but that message was rejected somewhere along
the line. In any event, your first reply did not solve my problem.
Thanks,
Ernie

 

[Dave Peterson]

First, it's better to keep all posting in the newsgroups. That way anyone can
help.

Second, I didn't see a reply to this message:

http://groups.google.co.uk/group/mi...ject:workbooks"&rnum=1&hl=en#e03ee460d62b94ad

or

http://snipurl.com/m4cc

Third, you can always use Google to search for previous messages (and
responses):
http://groups.google.com/advanced_group_search

Dave,
I saw your reply on 1/31/06. I think there was an earlier one that referred
me to Walkenbach. That message got deleted by mistake. I did reply to it
directly to your email address but that message was rejected somewhere along
the line. In any event, your first reply did not solve my problem.
Thanks,
Ernie

 

[Ernest L. Lippert]

Dave,
Thanks for the advice about newsgroups. I'm still looking for a solution to
my Excel problem. I apparently still have a problem with making code Public
and with using the Application event.
Regards,
Ernie

 

[Dave Peterson]

I'm not sure what you mean by Public.

Do you mean share with others?

Create an addin (File|saveAs, choose save as type addin) and share that files
with others.

Tell them to use tools|addins to install it.

Dave,
Thanks for the advice about newsgroups. I'm still looking for a solution to
my Excel problem. I apparently still have a problem with making code Public
and with using the Application event.
Regards,
Ernie