Add-in won't work

[Ernest L. Lippert]

The BeforePrint code below, when placed in This Workbook object, works fine.
However, when it is saved as an xla file to create an Add-In, it doesn't
work in another workbook even though the Add-In "FooterYesNo" is checked in
the Add-In Manager.



What haven't I done correctly?

Regards,

Ernie

*********************************

Option Explicit

Dim Ans As String

Dim sht As Variant

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'Based on John Walkenbach, Microsoft Excel 2000 Power

'Programming with VBA, ISBN 0-7654-3263-4 (1999) p. 527

Ans = MsgBox("Add path to footer?", vbYesNo + vbQuestion, "Tell
Me")

If Ans = vbNo Then

For Each sht In ThisWorkbook.Sheets

sht.PageSetup.LeftFooter = ""

Next sht

Else: For Each sht In ThisWorkbook.Sheets

sht.PageSetup.LeftFooter = "&8" & LCase(ThisWorkbook.FullName)

Next sht

End If

Ans = MsgBox("Print this sheet?", vbYesNo + vbQuestion, "Tell me")

If Ans = vbNo Then Cancel = False

End Sub


********************-30*************

 

[Bob Phillips]

Option Explicit

Public WithEvents App As Application

Dim Ans As String

Dim sht As Variant

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Wn.Caption = Wb.FullName
End Sub

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
'Based on John Walkenbach, Microsoft Excel 2000 Power
'Programming with VBA, ISBN 0-7654-3263-4 (1999) p. 527

Ans = MsgBox("Add path to footer?", vbYesNo + vbQuestion, "Tell Me")

If Ans = vbNo Then

For Each sht In Wb.Sheets
sht.PageSetup.LeftFooter = ""
Next sht

Else
For Each sht In Wb.Sheets
sht.PageSetup.LeftFooter = "&8" & LCase(Wb.FullName)
Next sht

End If

Ans = MsgBox("Print this sheet?", vbYesNo + vbQuestion, "Tell me")

If Ans = vbNo Then Cancel = False

End Sub


Private Sub Workbook_Open()
Set App = Application
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

(remove nothere from email address if mailing direct)

 

[ernie]

Many thanks, Bob. Will give it a try later today.
Regards,
Ernie

 

[RB Smissaert]

ThisWorkBook now applies to the add-in and this is not getting printed.
So, the Workbook_BeforePrint event won't get triggered.

This is a way to handle this:

In your Add-in have this code in the Workbook_Open event, make sure it is
not in the Workbook_Open event
of the ActiveWorkbook:

Option Explicit
Dim WithEvents xlApp As Excel.Application

Private Sub Workbook_Open()
Set xlApp = Excel.Application
End Sub

Private Sub xlApp_WorkbookBeforePrint(ByVal Wb As Workbook, _
Cancel As Boolean)
Cancel = PrintSheetFooter()
End Sub


Then in your Add-in have this code in a normal Module:

Option Explicit

Function PrintSheetFooter() As Boolean

Dim ans As VbMsgBoxResult
Dim sht As Worksheet

'Based on John Walkenbach, Microsoft Excel 2000 Power
'Programming with VBA, ISBN 0-7654-3263-4 (1999) p. 527

ans = MsgBox("Add path to footer?", vbYesNo + vbQuestion, "Tell Me")

If ans = vbNo Then
For Each sht In ActiveWorkbook.Sheets
sht.PageSetup.LeftFooter = ""
Next sht
Else
For Each sht In ActiveWorkbook.Sheets
sht.PageSetup.LeftFooter = "&8" & LCase(ActiveWorkbook.FullName)
Next sht
End If

ans = MsgBox("Print this sheet?", vbYesNo + vbQuestion, "Tell me")

If ans = vbNo Then
PrintSheetFooter = True
End If

End Function


RBS

 

[Ernest L. Lippert]

The following code works fine as an Add-In. In addition to Bob Phillips'
advice, I received a second method but it was more complex. In Bob's code, I
deleted the lines



Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)

Wn.Caption = Wb.FullName

End Sub



Since it didn't seem to add functionality. Thanks for the help.



Regards,

Ernie



VBA footer.xls








This Add-In (.xla file) asks BeforePrint (or Preview) Yes/No to add a
footer with filename


This is the final version 3-5-06. The PrintOption is commented out
























This code is in 'VBA Footer.xls' This Workbook object





##################################################


Option Explicit








Public WithEvents App As Application






Dim Ans As VbMsgBoxResult







Dim sht As Worksheet

















Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, cancel As
Boolean)


'cancel = True is passed to the procedure, False cancels priniting



'Based on John Walkenbach, Microsoft Excel 2000 Power




'Programming with VBA, ISBN 0-7654-3263-4 (1999) p. 527




'With advice from Bob Phillips, EXCELTIP.com





Ans = MsgBox("Add path to footer?", vbYesNo + vbQuestion, "Tell Me")



If Ans = vbNo Then







For Each sht In Wb.Sheets







sht.PageSetup.LeftFooter = ""






Next sht








Else









For Each sht In Wb.Sheets







sht.PageSetup.LeftFooter = ""






sht.PageSetup.LeftFooter = "&8" & Lcase(Wb.FullName)




Next sht








End If









'Next two lines for debugging








'Ans = MsgBox("Print this sheet?", vbYesNo + vbQuestion, "Tell
me")



'If Ans = vbNo Then cancel = False






End Sub









Private Sub Workbook_Open()







Set App = Application







End Sub









##################################################












Preview Icon








Insert Footer Yes/No works OK







commented out:
Print Yes/No works OK by default: both Yes and No goto the Preview
Screen











Printer Icon








Insert Footer Yes/No works OK







commented out:
Print Yes/No always prints















File>Print









Insert Footer Yes/No works OK







commented out:
Print Yes/No works by Windows default














Ernie Lippert








2/5/2006

 

[Bob Phillips]

You are right about that. That is the app event that I use, and I just added
to it for you, but forgot to remove that bit. My bad.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Opornik]

Hi,
I have a similar problem but with "Workbook_SheetCalculate".
I have the following sub in my Add-in module (I've taken it somewhere from
Web):
--------------------
Sub AutoFilterStatusForce()
Dim r As Range, i As Integer
If ActiveSheet.FilterMode Then
For Each r In ActiveSheet.AutoFilter.Range.Rows
If Not r.Hidden Then i = i + 1
Next r
Application.StatusBar = CStr(i - 1) & " of " &
CStr(ActiveSheet.AutoFilter.Range.Rows.Count - 1) & " records found."
Else
Application.StatusBar = False
End If
End Sub
-------------------
It forces to display AutoFilter filtered records instead of just "Filter
Mode" in status bar even if there are lot of formulas in the sheet. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q189479

I can run it manually but I want it be be executed every time after the
active sheet is calculated. That is why I placed it in my add-in.
I tried to apply RB Smissaert solution for this but I couldn't make it
working. Does it differ so much it is OpenWorkbook or
Workbook_SheetCalculate?

Can anybody help me, please?
Thank you!
Jack