Disable Print Preview Excel 2007

[jgspencer]

Is it possible to disable print preview all together with VBA in 2007 and how can it be achieved? I've tried to close it by sending the escape key but this doesn't seem to work for me. Right now, I have code that cancels printing and print preview and then shows a userform. When you press the print button on the userform, it sets enable events to false and then prints a named range. The problem is if the user presses print preview, it displays the userform and when they select print, it takes them to print preview. Even worse, if they select the print icon and then cancel it after the print dialogue box displays, it causes the workbook to crash.

So I think my best choice would be to disable print preview. The page is already setup for the user so there would be no need to print preview. Any help would be greatly appreciated.

 

[Snakehips]

JG Not sure if you still have this issue and not sure if the following helps but here it is anyway.

I am slightly confused, which might just be me being a bit dim.

If the issue is with the user using the the standard Excel print/print preview options then I think you're stumped.
You can remove the icons from the Quick Access Toolbar but I don't think you can remove or disable the Microsoft Office Button drop-down option.

Otherwise, is that the Print button on your form is generating the MSO Print Dialog and they can select Print Preview from there?

I assume that your target sheet / print area are pre-selected, presumably via code.

The code line 'ActiveWindow.SelectedSheets.PrintOut' will print a single copy from the default printer without a preview and without displaying the Print Dialog.

 

[jgspencer]

Yes, I am still having this issue and have not been able to figure it out. I'm not sure it can even be done.

My issue is that I don't want the user to be able to use the Print Preview option in the Quick Access Toolbar or from the Office Button. I know I can remove it from Quick Access, but the user could just add it back. Any other work arounds that you can think of?

Thanks for your help.

 

[Snakehips]

JG I think I might have a work around for you.
Can you confirm or clarify the following points so that I can test my idea. That way I can hopefully avoid posting something that doesn't actually work.

1 You have code that cancels a standard Excel print or print preview command and loads a user form?
2 You have a 'Print' control button on the form, that runs code that prints a named range?
3 Does the form serve any purpose other than to display this one control?
4 If the user clicks the forms' 'Print' button only, all is good?
5 If they use any standard print / preview command whilst your form is visible you get various issues?
6 Does this situation apply to just one sheet?
7 Do you want to allow the user use of the standard print / preview commands elsewhere in the workbook?

Get back to me and I will test and respond asap.

Tony

 

[jgspencer]

Tony,

Below are the answers to your questions:

1. Yes, if the user selects quick print, print from the office button, or print preview, my code cancels it and then loads a userform.
2. I have two buttons on the userform, both print a pre-selected print range.
3. The only purpose of the form is to print 1 of 2 ranges, nothing else.
4. Yes, if the user selects one of the buttons to print, all is good, no errors.
5. Yes, if they select any standard print / print preview commands while the form is open, various issues can occur. Also, if they select the standard print preview from the Quick Access or the Office button initially, the userform appears. From here if they select one of the buttons on the userform, the named range previews. Then if they select the print option from print preview and then cancel the print dialogue box, excel crashes. This is one of the reasons I want to disable print preview altogether or at least a work around.
6. I have two sheets that use the same type of userform and code, so it affects two different sheets.
7. Yes, I have a total of 5 sheets, three of them use the standard print range. So on these sheets, it's okay to use the standard print / preview command.

A brief synopsis of what this workbook is designed for:

Our restaurants take orders over the phone for various food items, one sheet is for bulk to-go orders and the second sheet is for regular menu orders. When the order is complete for either one, the manager has the ability to print two forms, one for the kitchen to ring the order in and the second form is so the manager can print and fax an order confirmation to the guest if they want a receipt. The form faxed to the guest is a cleaner version that only lists the guest's information and their order, while the first form for the kitchen lists the entire order form. Guests would not understand this form. This is why I created a userform so that the manager can have the ability to print these different ranges within the workbook.

I control all of this with "Private Sub Workbook_BeforePrint" on ThisWorkbook based off the name of the sheet.

Select Case ActiveSheet.Name
Case "Large Party"
If WorksheetFunction.CountA(Sheets("Large Party").Range("d4, d6, k4, D10, d12, k6, k8")) < 7 Then
MsgBox "You MUST fill out the below before Printing:" & vbCrLf & "" & vbCrLf & "Contact Name," & vbCrLf & "Contact Phone," & vbCrLf & "Number of Guests," & vbCrLf & "Gratuity %," & vbCrLf & "Day and Date of Party," & vbCrLf & "Time of Party," & vbCrLf & "and" & vbCrLf & "Party Planner/Manager Name" & vbCrLf & "" & vbCrLf & "Printing Cancelled!", vbExclamation
Cancel = True
Exit Sub

Else

Cancel = True

ActiveWindow.WindowState = xlMinimized
UserForm2.Show
End If
End Select

Again, thanks for your help in this.

Jon

 

[Snakehips]

Jon,
Apologies for the fact that this is a bit long-winded.
At the moment I am struggling to fully replicate your issue with my test workbook.
Forms are 'Modal' by default which means that while they are visible, you can only use the form controls. You should not be able to get a response from the keyboard nor from any Excel feature via the mouse. This is so in my trial, so I find it hard to understand how your users can click the Excel Print or Print Preview and cause an issue whilst your form is displayed. You can load a form with it being 'Modeless', with the code MyFormName.Show 0 in which case you retain full Excel functionality. Also you can use code such as EnableWindow Application.hWnd, Modeless to force modeless operation at runtime. Have you inadvertently got such things anywhere in your code?

I did however find the following:
a) When the form is invoked by clicking Excel Print, the resultant print, after clicking the form's Print button is a direct print, even if the button is coded to do .PrintPreview.
b) When the form is invoked by clicking Excel Print Preview, the resultant print, after clicking the form's Print button is actually print preview, even if the button is coded to do .PrintOut.
c) If the form's Print button is coded such that it's printing takes place whilst the form still shows, then there is NO PROBLEM if the form has been invoked by clicking Excel Print.
d) If the form's Print button is coded such that it's printing takes place whilst the form still shows, then there IS A PROBLEM if the form has been invoked by clicking Excel Print Preview. Namely EXCEL FREEZES, with an image of the form in the preview pane.
e) The problem d) can be overcome if the form's Print button is coded so that a MyFormName.Hide preceeds the print command. Printing via the form even when the form was invoked via Excel Print Preview.

So even without your ability to get a response from the Excel commands with the form open it's obvious that things don't always run as you might expect with this approach.
Possibly something of the above might give you a clue as to how you might tweak what you have and straighten it out, especially item e)?
Excel can do some strange things at times. Often it's of our own doing but sometimes maybe not, and unless you're a real cleverdick it can be difficult to know why.

However, can I suggest that you consider a slightly different approach to the printing, which I know will be robust.
It will not involve a form, nor coding Application.EnableEvents = False, nor use of the Excel Print commands in the two sheets where you currently don't want their functionality.
There will be no need to minimise the window, which I assume you have done in order to avoid seeing any screen changes if you are printing off, say, your guest's receipt?

The worksheet's BeforePrint event outcome will be conditional upon a Global Variable - AllowPrint
Three of your sheets, with AllowPrint set = True, will retain Excel Print functionality.
Two of your sheets, with AllowPrint set = False, will not respond to the Excel Print & Preview commands at all, they will cancelled by default.
Each sheet's Activation Event will set AllowPrint appropriately.
The printing of your documents will be via two custom Print buttons on your data entry sheet.
The code for these buttons will temporarily modify AllowPrint so that a coded print command will be performed.
You can obviously add the bits of conditional code as required to suit your needs.

Broadly speaking, here's how it would work.
In a code Module - define a variable - Global AllowPrint As Boolean

In This Workbook -
Sub Workbook_BeforePrint (Cancel As Boolean)
'If your Print button has been clicked or you have used Excel Print
'in an appropriate sheet Then AllowPrint = True so just exit
If AllowPrint = True Then Exit Sub
'Otherwise AllowPrint = False so cancel printing
Cancel = True
End Sub

In each of your worksheets where you want to use the normal Excel Print & Print Preview create the following event procedure -
Private Sub Worksheet_Activate()
AllowPrint = True
End Sub

In your two worksheets where you don't want to use the normal Excel Print & Print Preview create the following event procedure -
Private Sub Worksheet_Activate()
AllowPrint = False
End Sub

Hopefully you have room on your data input screen for two ActiveX control buttons, each with a suitable name & caption, GuestPrint, OurPrint?
Set their PrintObject property to False - this means that they will remain visible on screen but will NOT appear on any printouts.

Code their Click events typically as -
Private Sub GuestPrint_Click()
'*** Could include your 'all items completed check' and error message here
' exit sub if false or carry on and print
'or with suitable logic this can remain in your new BeforePrint routine.
'***
Application.ScreenUpdating = False
Sheets("Guest Form Sheet?").Select 'if print area suitably pre-set
AllowPrint = True
ActiveWindow.SelectedSheets.PrintOut
AllowPrint = False
Sheets("Our Data Input Sheet?").Select

End Sub


Private Sub OurPrint_Click()
'*** Could include your 'all items completed check' and error message here
' exit sub if false or carry on and print
'or with suitable logic this can remain in your new BeforePrint routine.
'***
Application.ScreenUpdating = False
'assuming this form is a print from your currently selected data-input sheet?
'then no need to switch sheets otherwise create a mod of above
AllowPrint = True
ActiveWindow.SelectedSheets.PrintOut
AllowPrint = False


End Sub



I hope I've managed to make that sound half sensible and that it proves to be a valuable alternative.
It will not be prone to any of the current problems.
I will also be leaving you a private message.

Tony

 

[jgspencer]

Tony,

I answered wrong about having the ability to select print, print preview, etc. while the form is displayed. The form was Modal and only allows use of the form controls. Sorry about that.

Wow, thanks so much for your help. It worked perfectly. I did change my initial approach a little bit to disallow users the ability to print on any sheet. I didn't want to confuse them on which sheets they use the normal Excel printing and which sheets they use the print button. So, now on all sheets, they will print from the print button.

Thanks for your help in this. I don't think your post was too long at all. I would rather have more information than not enough.

So far, I have not had any issues with Excel crashing on me.