Why WorkbookBeforePrint doesn't run?

[RB Smissaert]

Have a .xla add-in (call it add-in A) that is not loaded (not ticked under
Tools, Add-ins) but opened by an installed (loaded add-in).
In this add-in A I have the following code:

In the workbook open event:
---------------------------------

Option Explicit
Private WithEvents xlApp As Excel.Application

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

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

In a normal module:
----------------------

Function PrintSheetFooter() As Boolean

'some code to set the footer

End Sub

The trouble is that Private Sub xlApp_WorkbookBeforePrint doesn't run and
the reason is that
when this event is happening xlApp is an empty variable.
When I add this Sub to the ThisWorkbook module:

Sub test()
Set xlApp = Excel.Application
End Sub

and run it from the VBE and do a print then Private Sub
xlApp_WorkbookBeforePrint will run
as expected.

I have tried with:
Public WithEvents xlApp As Excel.Application and
Dim WithEvents xlApp As Excel.Application

Set xlApp = Excel.Application definitely runs in the Workbook_Open event as
I have tested that with
a msgbox. So why is it that xlApp is nothing when it is needed?

I have tried with a normal class module instead of the ThisWorkbook class
module, but it is just the same.

On the other hand when I do the same thing in the add-in that does get
loaded (call it Add-in B) it works fine.
The problem is I can't put it in Add-in B as it doesn't know about certain
variables that are in Add-in A, although
there should be a work-around for that.
I just think that this must be possible and I am overlooking something
essential here.
Thanks for any advice.


RBS

 

[davesexcel]

where are you placing the workbook before print code

 

[RB Smissaert]

Yes, I mis-typed that.
I said: In the workbook open event

But that should be in the ThisWorkbook module of the add-in.

RBS