MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Disable Printing

Posted by Jamie on April 03, 2000 12:51 PM

Hi all,
I've got a file in excel 97 that I need people to print out using a "print form" macro that I created otherwise, the form won't print correctly so...what I'd like to be able to do is disable the print button on the standard toolbar and the "Print..." option from the FILE menu. This way, they would have no other choice but to use my macro to print the form. But, I want to disable these two things only for this one particular file, not as a global setting. How can I do this?
Appreciate any help that I can get.

Posted by Ivan Moala on April 03, 2000 11:20 PM

One way to do this;
In your ms excel object Thisworkbook
use this routine;

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If PrtOK Then
Cancel = False
MsgBox "Can't print from here!"
Cancel = True
End If
End Sub

In your Modules have a routine like this;

Public PrtOK As Boolean

Sub PrintNow()
PrtOK = True
End Sub

Sub DontPrintNow()
PrtOK = False
End Sub

Now in your Userform before you print
put in a call to the;
PrintNow routine
eg Call PrintNow or PrintNow

THEN at the end of the routine
put in a call to the;
DontPrintNow routine
eg Call DontPrintNow or DontPrintNow

As the routine is in the Thisworkbook object it will only
apply to this work book and the print will be enabled for
other workbooks.


Posted by Jamie on April 04, 2000 4:39 PM


thanks for the post. Your code works perfectly.