ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Why WorkbookBeforePrint doesn't run? (https://www.excelbanter.com/excel-programming/355112-why-workbookbeforeprint-doesnt-run.html)

RB Smissaert

Why WorkbookBeforePrint doesn't run?
 
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[_36_]

Why WorkbookBeforePrint doesn't run?
 

where are you placing the workbook before print code


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519039


RB Smissaert

Why WorkbookBeforePrint doesn't run?
 
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


"davesexcel" wrote
in message ...

where are you placing the workbook before print code


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile:
http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519039




All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com