Printing

[poleary2000]

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.

[Al Chara]

What do you mean by deactivate? You can always delete the icons from the toolbars.

[dk]

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

[Tom Schreiner]

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

[Tom Schreiner]

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

[poleary2000]

Thanks. Works like a charm!

[poleary2000]

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.

[Tom Schreiner]

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

[poleary2000]

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!!!

[poleary2000]

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?