MrExcel Publishing
Your One Stop for Excel Tips & Solutions

disable print preview


Posted by Jason Spears on March 28, 2001 7:30 PM

Is there any way to disable the print preview function? I have several pages in a workbook, and as a before_print event I have

Private Sub Workbook_BeforePrint(Cancel As Boolean)

if userform1.visible=false then
cancel=true
userform1.show
end if

for the userform1 it has several checkboxes and two buttons (print and Cancel)

print goes through and prints the checked pages
cancel just unloads the form

everything works fine except if you push print preview the form will load and if you press cancell the system hang. Is there any way to disable the print preview, or have the code ignor the print preview command?

Thanks

Jason


Posted by David Hawley on March 29, 2001 3:25 AM

Hi Jason

The code works OK on my PC with a userform and a Cancel button. But anyway, the code below will disable the print preview and enable it again after deactivation.


Place it in the Workbook module with the Cancel print code.


Private Sub Workbook_Activate()
Dim MyPrint As CommandBar
Set MyPrint = Application.CommandBars("Worksheet Menu bar")

With MyPrint
.Controls("&File").Controls("Print Pre&view").Enabled = False
End With
Set MyPrint = Application.CommandBars("Standard")

With MyPrint
.Controls("Print Pre&view").Enabled = False
End With

End Sub


Private Sub Workbook_Deactivate()
Dim MyPrint As CommandBar
Set MyPrint = Application.CommandBars("Worksheet Menu bar")

With MyPrint
.Controls("&File").Controls("Print Pre&view").Enabled = True
End With
Set MyPrint = Application.CommandBars("Standard")

With MyPrint
.Controls("Print Pre&view").Enabled = True
End With
End Sub

Dave

OzGrid Business Applications

Posted by Jason Spears on March 29, 2001 7:01 AM

It works great.
Thanks
Jason