Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Temporary Interception of Print Command (Excel 97 VBA)

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

    Situation: I want to intercept the Print Command (Menu/Icon), reason see in code header:
    <pre>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
    </pre>



    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 ?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

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

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    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)
    [img]/forums/images/smilies/cool.gif[/img] 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.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    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. <LI>Open a new blank Workbook
      <LI>Open the VBE
      <LI>Insert a new Class Module and Rename it to "EventHandler"
      <LI>Add the following line of code in the General Declarations Section of the Class Module
      <pre>Public WithEvents WorkBookHandler As Excel.Application</pre>

      <LI>Create a New Module
      <LI>Add the following line of code to the General Declarations section
      <pre>Public ClassHandlingRoutine As EventHandler</pre>

      <LI>Now add the following Procedure to the module
      <pre>Public Sub CreateEventHandler()
      Set ClassHandlingRoutine = New EventHandler
      Set ClassHandlingRoutine.WorkBookHandler = Excel.Application
      End Sub</pre>

      <LI>Now the last bit of code goes in the WorkBook_Open event of the ThisWorkbook Module
      <pre>CreateEventHandler</pre>

      This will start Create a new Public Event Handler when this workbook is opened.
      <LI>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:
    <pre>MsgBox "This BeforePrint Will always fire as an add-in"</pre>


    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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    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.
    Attached Files Attached Files
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    Hi Bryan,
    <hr>
    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.
    <hr>
    Out of interest, what difference does it being an add-in make? I can do that with a plain workbook too.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    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)

    <pre>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
    </pre>


  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    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.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    Glad it works for what you need.
    <hr>Option Explicit 'to prevent erros arising from typos<hr>
    Uh, yea. Forgot that. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    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.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    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! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    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.)
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    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

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

    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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15> and figure them out.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    Bryan,
    I think we're talking at slight cross-purposes here! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> 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:
    <hr>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.<hr>
    and
    <hr>
    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.<hr>
    if that makes it any clearer! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Interception of Print Command (Excel 97 VBA)

    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. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Interception of Print Command (Excel

    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)

    <pre>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</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •