Disable Print, except using macro

[cjsmile2016]

Hello All,

I'm hoping someone can help, we have a lot of wizards in this forum! I need to create some how to disable the standard print menu's and be able to print only from a custom macro. Is this possible? Thanks!!

Tim

 

[Greg Truby]

While you can certainly try to hunt down and disable every Print command, it ain't as easy as it sounds. What if you have a user who has customized his toolbars?

It might be easier to use the workbook's _BeforePrint event handler to do what you want. You can either "fix" the "problem" or else use a global boolean to see how they got to the _BeforePrint event and if they didn't get there from your macro send them a message box and set CANCEL to TRUE and make them come in via your macro.

{Edit - forgot my manners - Welcome to the Board!}

HTH

 

[cjsmile2016]

I terribly sorry, but I have no clue how to do that. I'm still fairly new at VB (learning stages). Any example would help!

I tried using the following that I found, but when i run the Printnow macro it doesn't print.


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

Thanks, and thanks for the welcome!

 

[Greg Truby]

Well, you do sorta hafta tell it to print! :wink:

Standard Module
<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> booOKToPrint <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> PrintMePlease()
    booOKToPrint = <SPAN style="color:#00007F">True</SPAN>
    ActiveSheet.PrintOut
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Workbook module:
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> booOKToPrint <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Please use dadeedum to print", vbCritical, "Print Canceled"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Cancel = <SPAN style="color:#00007F">Not</SPAN> booOKToPrint
    booOKToPrint = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

HTH

 

[cjsmile2016]

Thank you very much!!

 

[cjsmile2016]

I attempted to make the code private by adding Private to the Standard Module (see below). When I do this the hot key will not work. I'm assigning the macro a hot key of Ctrl+q, but It doesn't work when I set it to private. Is there a way to make this work?

Standard Module:
Public booOKToPrint As Boolean

Private Sub PrintMePlease()
booOKToPrint = True
ActiveSheet.PrintOut
End Sub


Workbook module:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Not booOKToPrint Then
MsgBox "Please use dadeedum to print", vbCritical, "Print Canceled"
End If
Cancel = Not booOKToPrint
booOKToPrint = False
End Sub[/code]