Results 1 to 8 of 8
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disable Print from File Menu (XL 2002)

    I have some wonderful print macros in spreadsheets that are button driven from a control sheet. the only problem is the user who cannot find the control sheet and tries to print from the file menu. Can I disable the file menu print command (or insert a msgbox that says "print using macro (yes) or print normally (no)" with vbyesno) for this workbook only?

    How about doing exactly the same thing in word? How do you put in a button on a word document?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

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

    Re: Disable Print from File Menu (XL 2002)

    The following code, placed in the workbook before print event routine, will disable printing from either the File menu print command or from the tool bar print button.

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
    MsgBox "Please use the print buttons on the Control Sheet."
    End Sub
    </pre>


    If you do this, then you will want to put the following line at the beginning of your print macro:

    <pre> Application.EnableEvents = False
    </pre>


    And this line at the end of your print macro:

    <pre> Application.EnableEvents = True
    </pre>


    You could also replace the Msgbox in the first macro with a call to your macro.
    Legare Coleman

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Print from File Menu (XL 2002)

    Thanks Legare - I finally got around to using this code and it works like a dream. I did put in a checkbox so the user has the option of interrupting the print dialog or not. However, I did notice that this also fires if I click on print preview, not the print. This makes sense but is there a way to let the print preview through but catch the print?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Disable Print from File Menu (XL 2002)

    Since there is no way to tell if a preview or a print was requested (as far as I know), How about this?

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim iResponse As Integer
    iResponse = MsgBox(prompt:="Do you want Print Preview?", _
    Buttons:=vbYesNo, Title:="Preview?")
    Cancel = True
    Select Case iResponse
    Case vbYes
    Application.EnableEvents = False
    ActiveSheet.PrintOut Preview:=True
    Application.EnableEvents = True
    Case Else
    MsgBox "Please use the print buttons on the Control Sheet."
    End Select
    End Sub</pre>


    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Disable Print from File Menu (XL 2002)

    The BeforePrint event doesn't distinguish between previewing and printing. You could use a global variable to keep track of what the user does, but I don't know if it's worth the trouble. See this newsgroup thread.

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

    Re: Disable Print from File Menu (XL 2002)

    Since Excel does not distinguish between the two, and gives you no way to know which the user clicked, I don't see any way to do what you want. I also don't know why you would want to allow the user to preview what they can't print.

    Instead of using this routine, you could remove the Print command from the File menu and the Toolbar when the workbook is activated. However, you have to be very careful to put them back before the workbook is deactivated.
    Legare Coleman

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

    Re: Disable Print from File Menu (XL 2002)

    As from Excel 2000, you can hook an event to any menu control and thus catch what has been clicked.
    See the attached workbook.

    Code adapted from this excellent book.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Disable Print from File Menu (XL 2002)

    Nice. Thanks for that.
    Legare Coleman

Posting Permissions

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