Disable Print from File Menu (XL 2002)

Topic

New Reply

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?

Reply | Quote

Replies

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.

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

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

    Application.EnableEvents = False

And this line at the end of your print macro:

    Application.EnableEvents = True

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

Reply | Quote

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?

Reply | Quote

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

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

Steve

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Nice. Thanks for that.

Reply | Quote