Disable Print, except using macro

cjsmile2016

New Member
Joined
Jan 24, 2005
Messages
14
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,020
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

New Member
Joined
Jan 24, 2005
Messages
14
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,020
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

New Member
Joined
Jan 24, 2005
Messages
14
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]
 

Forum statistics

Threads
1,147,688
Messages
5,742,629
Members
423,745
Latest member
rtaylor1987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top