Temporary Interception of Print Command (Excel 97 VBA)

Topic

New Reply

Already asked this question in http://www.experts-exchange.com/Applicatio…Q_20390478.html%5B/url%5D, but no solution up to now.

Situation: I want to intercept the Print Command (Menu/Icon), reason see in code header:

Sub SafePrint()
 'Xerox Document Center 332ST chokes and crashs if paper not according its setup
 'Happens too often, Xerox and/or our Hotline unable/unwilling to find a solution
 'Idea: Re-route print command and check/correct paper setting first
 'Drawback: Requires assigning macro to _Standard_ Toolbar 
 'which will cause trouble if sub not loaded
 '=> Abandoned until failsafe solution is found
 With ActiveSheet.PageSetup
   If .PaperSize  xlPaperA4 And .PaperSize  xlPaperA3 Then
     MsgBox "Paper neither A4 nor A3 ! Change first !", vbCritical, "Safe Print"
     Exit Sub  'To do: Add code to change paper selection
   Else
     'To do: allow printing of range selections / whole workbooks too
     ActiveWindow.SelectedSheets.PrintOut
   End If
 End With
End Sub

Question: I do not want to exchange the problem, therefore I need a failsafe solution, i.e. if add-in not present, Excel has its standard behaviour.
Or do you see a totally different approach ?

Reply | Quote

Replies

You can use the Workbook BeforePrint event routine to intercept the Print command.

Reply | Quote

LegareColeman, thank you for tackling this question.

I should have given more background. AFAIK – would be glad if otherwise – the BeforePrint Event applies to the workbook which contains it. This would require all workbooks to obtain this code. This is not viable:
a) If I could force the people sending us workbooks from abroad to insert the code it would be easier to have them set the pagesettings to A4/A3, less macro warnings >8o)
Average user level is much below using VBA.
Therefore I want to insert this re-routing in my add-in xla which I develop as sideline, where I try to overcome the deficiencies of Excel which bothers me or my colleagues.

Reply | Quote

With an Add-in you can expose and HiJack more objects than are normally exposed if you declare a Public Excel object WithEvents in a Class Module.

What did I just say?

OK, the easiest way to explain it is to tell you HOW to do it.

So here are the steps

1. Open a new blank Workbook
2. Open the VBE
3. Insert a new Class Module and Rename it to “EventHandler”
4. Add the following line of code in the General Declarations Section of the Class Module

   Public WithEvents WorkBookHandler As Excel.Application

5. Create a New Module
6. Add the following line of code to the General Declarations section

   Public ClassHandlingRoutine As EventHandler

7. Now add the following Procedure to the module

   Public Sub CreateEventHandler()
     Set ClassHandlingRoutine = New EventHandler
     Set ClassHandlingRoutine.WorkBookHandler = Excel.Application
   End Sub

8. Now the last bit of code goes in the WorkBook_Open event of the ThisWorkbook Module

   CreateEventHandler

   This will start Create a new Public Event Handler when this workbook is opened.

9. Save this as an XLA Add-In

Now that you have the base setup open the Class Module.

From the Object combo select WorkBookHandler.

In the Procedure combo, there are all the event that are exposed.

The one that you would need is the WorkbookBeforePrint

Select WorkbookBeforePrint and add the folowing line of code to the event stub that was created:

MsgBox "This BeforePrint Will always fire as an add-in"

Now save the Add-In again.

Restart Excel and make sure that this add-in is loaded. (Tool|Add-Ins and make sure there is a check beside this add-in)

Now open any other workbook and try and print it.

You should get the message box popping up before the workbook gets printed.

I am also attaching an add-in that does what I just described here, so you can cheat if you want

Now your code should replace the message box we added to the WorkbookBeforePrint event.

Good luck and ask if I haven’t been clear.

BTW, this method will work in Word too. That is where I got the idea from.

Reply | Quote

Hi Bryan,
[indent]

---

With an Add-in you can expose and HiJack more objects than are normally exposed if you declare a Public Excel object WithEvents in a Class Module.

---

[/indent]
Out of interest, what difference does it being an add-in make? I can do that with a plain workbook too.

Reply | Quote

I don’t think there is any difference between putting it an Add-In and a regular Workbook.

The only reason I suggested (and tried it in) an Add-In was so that it was always available, and could be turned on or off at will.

I typically don’t work in Excel so there may be better place to put it in Excel.

Reply | Quote

Oh OK. I was intrigued by the “more objects” part of your answer – just thought I might be missing out on some functionality that could make my life even easier!
Thanks for the clarification. (I do incidentally always put my app-level stuff into an add-in rather than say personal.xls for exactly the reason you mentioned – i.e. being able to turn it on or off at will.)

Reply | Quote

Rory,

There may be more Events Exposed when you use WithEvents.

Here is a list of the events that are exposed in Excel 2000 (I forgot that we are dealing with XL97 and I don’t have access to 97 here at work, just 2000

Standard Exposed Events	WithEvents Exposed Events
Activate AddinInstall AddinUninstall BeforeClose BeforePrint BeforeSave Deactivate NewSheet Open SheetActivate SheetBeforeDoubleClick SheetBeforeRightClick SheetCalculate SheetChange SheetDeactivate SheetFollowHyperlink SheetSelectionChange WindowActivate WindowDeactivate WindowResize	NewWorkbook SheetActivate SheetBeforeDoubleClick SheetBeforeRightClick SheetCalculate SheetChange SheetDeactivate SheetFollowHyperlink SheetSelectionChange WindowActivate WindowDeactivate WindowResize WorkbookActivate WorkbookAddinInstall WorkbookAddinUninstall WorkbookBeforeClose WorkbookBeforePrint WorkbookBeforeSave WorkbookDeactivate WorkbookNewSheet WorkbookOpen

As you can see the exposed events are different. Like I said in an earlier post, I don’t use Excel a lot, so I’m not really sure what the various events are for, but the Excel Experts around here should be able to figure them out.

That or you can play with them and figure them out.

Reply | Quote

Bryan,
I think we’re talking at slight cross-purposes here! I misinterpreted your original quote as meaning that you could expose more events using WithEvents in an add-in than if you used WithEvents in a standard workbook. I guess it’s a subtle difference between:
[indent]

---

With an Add-in you can expose and HiJack more objects than are normally exposed if you declare a Public Excel object WithEvents in a Class Module.

---

[/indent]
and
[indent]

---

With an Add-in you can expose and HiJack more objects than are normally exposed, if you declare a Public Excel object WithEvents in a Class Module.

---

[/indent]
if that makes it any clearer!
I’m not going to claim to be an expert but I am familiar with the events themselves, I just thought I might have missed an added feature of add-ins.

Reply | Quote

I think we are. Probably because of the way I worded it.

Reading the excerpt you included, made me realize that I did INDEED word it wrong.

Add-Ins have nothing to do with the WithEvents exposing more events. WithEvents in general expose more events that are regularly available.

I just checked, and WithEvents in a regular Workbook and WithEvents in an Add-In will expose the same events.

Lets just say that writing and grammar aren’t my strong suits.

Reply | Quote

I couldn’t find, in Word2000, the event that corresponds to Excel2000’s SheetFollowHyperlink so I wrote this.
(The calls to standalone applications Indxr and Trail represent macros that i run to process the hyperlinked document before I hand it to the end-user)

Public WithEvents WorkBookHandler As Word.Application

Private Sub WorkBookHandler_WindowBeforeRightClick(ByVal Sel As Selection, Cancel As Boolean)
    ' MsgBox "you right-clicked"
    
    If Selection.Hyperlinks.Count > 0 Then ' we are in a hyperlink
        Cancel = True ' avoid the follow-on
        Dim strAddress As String
        strAddress = Selection.Hyperlinks(1).Address
        Dim doc As Document
        Set doc = Documents.Open(strAddress)
        doc.Activate
        Call Indxr.IndexActiveDocument
        Call Trail.TrailActiveDocument
    
    Else
    End If
End Sub

Reply | Quote

Perfect ! carbonnb, thanks a lot, especially for the step-by-step instruction. Up to now I stayed clear of messing with Class Modules.

Here the code which I use (now)

Option Explicit  'to prevent erros arising from typos
Public WithEvents WorkBookHandler As Excel.Application

Private Sub WorkBookHandler_WorkbookBeforePrint(ByVal Wb As Excel.Workbook, Cancel As Boolean)
  'Xerox Document Center 332ST chokes and crashs if paper not according its setup
  'Happens too often, Xerox and/or our Hotline unable/unwilling to find a solution
  With ActiveSheet.PageSetup
    If .PaperSize  xlPaperA4 And .PaperSize  xlPaperA3 Then
      Cancel = (MsgBox("Paper neither A4 nor A3 ! Cancel Printjob ?", vbYesNo, "SafePrint") = 6)
    End If
  End With
End Sub

Reply | Quote

Glad it works for what you need.
[indent]

---

Option Explicit ‘to prevent erros arising from typos

---

[/indent]Uh, yea. Forgot that.

I turn that on Require Variable Declarations when I get a new install of Office as part of my “personalisation” of the various Office apps, so I assume everyone has it on.

Reply | Quote