Archive of Mr Excel Message Board


Back to Printing in Excel archive index
Back to archive home

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.
Thanks,
Jamie

Re: Disable Printing

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

Jamie
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
Else
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.
HTH

Ivan


Thanks

Posted by Jamie on April 04, 2000 4:39 PM
Ivan,
thanks for the post. Your code works perfectly.
Jamie

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.