Printing

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
Does anyone know if there is a way to deactivate printing / print previewing through the use of VBA code?

Based on a value stored in a worksheet or variable [i haven't decided], I would like to turn this feature on or off.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
What do you mean by deactivate? You can always delete the icons from the toolbars.
 
Upvote 0
Hi,

Try this. Open the VB editor, click Ctrl R to open the Project explorer and then double click the ThisWorkbook icon of your workbook. This code should do what you need:-

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheets("sheet1").Range("a1") <> "CAN PRINT" Then Cancel = True
End Sub

HTH,
Dan
 
Upvote 0
I think he/she wants to disable the menuitem.
You can do this in VBA.
I do not know the syntax. Will do a search
Tom
 
Upvote 0
Application.CommandBars("Worksheet Menu Bar").Controls _
("File").Controls("Print Preview").Enabled = False

Application.CommandBars("Standard").Controls _
("Print Preview").Enabled = False


The following two lines of code will disable the print preview on the Worksheet Menu Bar - File menuitem and the Print Preview on the standard toobar...

However, you will need to disable custimization. The user can still create their own button.

Tom
 
Upvote 0
Okay - this works absolutely great for PrintPreview. However, it does not work for regular Print. I have modified it so it does work for the Menu listing "Print...". However, it does not work for the toolbar. I believe this is because the toolbar is called Print(Default Printer). How can I find the default printer and make this icon unavailable.
 
Upvote 0
Hi
This will disable any button in any commandbar which begins with "Print"
To re-enable, copy into another sub and change the false to true...

Sub DisableCommandBarControl()
Dim cb As CommandBar
Dim bb As CommandBarControl

For Each cb In CommandBars
For Each bb In cb.Controls
If Left(bb.Caption, 5) = "Print" Then _
bb.Enabled = False
Next
Next

End Sub

Tom
 
Upvote 0
Tom,

Thanks for all of your help. I tried to figure this out for about 2 hours last night with no success. I did not know about the bb.

Thanks!!!
 
Upvote 0
Tom,

Thanks again for the help. I have one more question however. I have setup two subs - one call Sub EnablePrint() and the other Sub DisablePrint(). I use these by calling them when I need them.

However, I want to call the EnablePrint whenever the workbook or application is closed. I am trying to do this by using the following in the ThisWorkbood tab

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call EnablePrint

End Sub

This gives me an error every time. Sometimes Excel just locks up. Any suggestions?
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top