Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Eugene, Oregon, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cancel BeforePrint Event? (2000/SR-1)

    I'm writing a BeforePrint event in a class module. My understanding was that the "Cancel" parameter would allow me to stop the print process, but it doesn't seem to work--the normal Excel Print dialog box appears anyhow. (I normally program in Word VBA--is there something different about class modules and app events in Excel?)

    In any case, here's my test code.



    Private Sub ExcelApp_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
    MsgBox "App_DocumentBeforePrint"
    Cancel = True
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cancel BeforePrint Event? (2000/SR-1)

    I assume the msgbox does show up?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Eugene, Oregon, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cancel BeforePrint Event? (2000/SR-1)

    Yup. I get the msgbox followed by the default print dialog.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Eugene, Oregon, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cancel BeforePrint Event? (2000/SR-1)

    EEEKKK!

    There's a bug notice regarding the WorkbookBeforePrint events that is posted on the Microsoft KB (the Cancel parameter causes Excel to crash). I guess my problem with the Cancel parameter failing to trigger sort of pales in comparison! (It's evidently been fixed in XP-SP1).

    Oh well.

    Does anyone know of an alternative means for intercepting the File|Print command in Excel (like declaring a "Public Sub FilePrint" in Word?)

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cancel BeforePrint Event? (2000/SR-1)

    Hubert, without knowing the background of what you are doing and therefore if it is applicable, can you use the Workbook_BeforePrint Event in the ThisWorkBook Object. In the VBE, under the Project Explorer, double click the ThisWorkbook Object; in the Object drop down (the left of the two drop-downs atop the code window), Select Workbook; from the right side Procedure (Declarations/Events) drop down, an Open event will be created, scroll back up the drop-down and you find BeforePrint.

    Is this any help?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Eugene, Oregon, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cancel BeforePrint Event? (2000/SR-1)

    Thanks John. Unfortunately, the same bug warning applies to the ThisWorkbook object.

    Also, I'm trying to implement some code in an add-in template that would intercept the print command for all documents and to capture the number of pages printed and send that information into a database for charging the print-outs to clients. I'm new to Excel VBA (more comfortable in the Word VBA environment) but I didn't think I could use the ThisWorkbook object in a global, add-in template. (I couldn't get the BeforePrint event to fire from this object).

    Any other ideas?

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cancel BeforePrint Event? (2000/SR-1)

    I guess a workaround could be to disable all possible ways a user can invoke the printcommand and create your own print toolbar button.
    That requires quite some work, since there are a lot of ways to print:

    - File, print
    - File, Print preview
    - The print button on the File, pagesetup dialog
    - The print-preview button on a toolbar
    - Control-p keyboard shortcut
    - others ????


    Here is something that seems to do the job of disabling:

    <pre>Option Explicit

    Sub DisablePrint()
    Dim oCommandbar As CommandBar
    Dim oControl As CommandBarControl
    For Each oCommandbar In Application.CommandBars
    'File, Print...
    Set oControl = oCommandbar.FindControl(ID:=4, recursive:=True)
    If Not oControl Is Nothing Then
    oControl.Enabled = False
    End If
    'Print Preview
    Set oControl = oCommandbar.FindControl(ID:=109, recursive:=True)
    If Not oControl Is Nothing Then
    oControl.Enabled = False
    End If
    'Page Setup
    Set oControl = oCommandbar.FindControl(ID:=247, recursive:=True)
    If Not oControl Is Nothing Then
    oControl.Enabled = False
    End If
    'Print button on toolbar
    Set oControl = oCommandbar.FindControl(ID:=2521, recursive:=True)
    If Not oControl Is Nothing Then
    oControl.Enabled = False
    End If
    Next
    Application.OnKey "^p", ""
    End Sub
    Sub EnablePrint()
    Dim oCommandbar As CommandBar
    Dim oControl As CommandBarControl
    For Each oCommandbar In Application.CommandBars
    'File, Print...
    Set oControl = oCommandbar.FindControl(ID:=4, recursive:=True)
    If Not oControl Is Nothing Then
    oControl.Enabled = True
    End If
    'Print Preview
    Set oControl = oCommandbar.FindControl(ID:=109, recursive:=True)
    If Not oControl Is Nothing Then
    oControl.Enabled = True
    End If
    'Page Setup
    Set oControl = oCommandbar.FindControl(ID:=247, recursive:=True)
    If Not oControl Is Nothing Then
    oControl.Enabled = True
    End If
    'Print button on toolbar
    Set oControl = oCommandbar.FindControl(ID:=2521, recursive:=True)
    If Not oControl Is Nothing Then
    oControl.Enabled = True
    End If
    Next
    Application.OnKey "^p"
    End Sub
    </pre>

    You would have to call the DisablePrint sub from the thisworkbook_Open event sub of your add-in and call EnablePrint from the thisworkbook_BeforeClose event sub.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Eugene, Oregon, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cancel BeforePrint Event? (2000/SR-1)

    Thanks Jan.

    It turns out that one of the COM Add-ins that our firm uses has its own BeforePrint event that actually works (which makes me think that there must be some workaround for the MS bug on this issue). In any case, I'm going to use that event which gets me off the hook from having to intercept all those commands/buttons/keyboard shortcuts, etc.

    However--the BeforePrint event appears to be triggered when the user selects PrintPreview. Is there a way to tell how the BeforePrint event is triggered (whether from the Print command or the PrintPreview command)? Or would I need to use your code to intercept the PrintPreview commands/buttons/etc. to set a parameter that would enable me to ignore the BeforePrint event when the user is PrintPreviewing?

    Thanks again for all the coding! I know that even if I don't end up using it for PrintPreview here, I'll have a need for it in the near future!

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cancel BeforePrint Event? (2000/SR-1)

    Avoiding printpreview to trigger the before_print event is almost impossible.

    There is a possibility to check whether printpreview has been invoked, but only *after* XL actually gets into print preview.

    It requires use of API calls to:

    - start a VBA sub using the SetTimer API
    - grab the window handle of the print-preview window (or something like it). If it's there, take appropriate action.

    Disabling the printpreview commandbuttons and menus is not of much help, since there is no simple way of disabling the button on the normal print dialog. Also, shift-clicking the normal print toolbar button (which you did not disable <g>) gets you into preview mode.

    Sorry, no sigar.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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