Print Preview (Setup,Margins,Page Break Preview) Disable

max_lux · Jan 12th 2006

I know that the thread :

http://www.ozgrid.com/forum/showthread.php?t=15271

was closed because everyone thought the problem was solved, but it was only partially solved. The question was "How to disable the setup and margin buttons when in print preview?".

The answer came as to use the following code...

Code

ActiveSheet.PrintPreview False

...which does actually grey the two buttons, but then, if one selects page break preview, it ungreys them and access is given to those two buttons.

Therefore, my question is, does anyone or can anyone perhaps think of a property/ method that might be used to remove the "page break preview button" as well?

Thanks in advance,

Max

Oh, by the way, this is an awesome forum and website. Many thanks to whomever run(s) it.

max_lux · Jan 12th 2006

Re: Print Preview (Setup,Margins,*Page Break Preview*) Disable

On a further note, the codes:

Code

ActiveSheet.PrintPreview False

and

Code

Application.Dialogs(xlDialogPrintPreview).Show False

Both do the same thing, grey out the setup and margin buttons, but:

1) if you press the print preview button, it enables the two buttons and keeps the print preview open. If you press normal view (second press of the same button) it will exit print preview.

2) if you press the close button, it enables the two buttons and keeps the print preview open. If you press close (second press of the same button) it will exit print preview.

Dave Hawley · Jan 12th 2006

Re: Print Preview (Setup,Margins,*Page Break Preview*) Disable

Hi Max

Try

Code

Private Sub Workbook_Activate()
Dim oCtrl As Office.CommandBarControl
 
'Disable all print menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=109)
            oCtrl.Enabled = False
     Next oCtrl
    
'Disable all print menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=4)
            oCtrl.Enabled = False
     Next oCtrl
     
End Sub






Private Sub Workbook_Deactivate()
Dim oCtrl As Office.CommandBarControl
 
'Enable all print menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=109)
            oCtrl.Enabled = True
     Next oCtrl
    
'Enable all print menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=4)
            oCtrl.Enabled = True
     Next oCtrl




End Sub

Display More

Then add your own custom Print button and have it activate

Code

Sub StopPrintView()
    Application.Dialogs(xlDialogPrint).Show , , , False, False
End Sub

max_lux · Jan 12th 2006

Re: Print Preview (Setup,Margins,*Page Break Preview*) Disable

Thanks for the help, but I think you misunderstood what I am looking to do.

I have a workbook which I am trying to make as foulup free as possible, because it must be distributed to over 50 people, and some of them are still learning Windows 95 (well not quite, but I hope you get my point; most of them are not advanced users). One guy still runs an old laptop with a 640 by 480 screen even!

What I have done is:
- disabled worksheet menu bar and close all other toolbars
- disabled commandbar functions (min,restore,exit)
- disabled toolbar customization (add,edit buttons)
- disabled right click (context menus)
- disabled toolbar list (access to running another toolbar which the user has edited and added buttons of their choosing)
- all actions are performed by macros initiated by buttons, other than the default shortcut keys
- I have one sheet very hidden but which becomes visible when either print previewing or printing only, initiated by a button as described above. Lets call this sheet Sheet1Report

My problem:

I have a report (Sheet1Report) which I need the user to be able to print preview prior to printing (error checking of visually contiguous information), however, I do not wish the user to be able to edit margins or print settings. I cannot successfully disable the functions which will stop the user from being able to do this. I can grey them out for initially, but they become functional upon a keypress, therefore the previously quoted lines of code do not fulfill the requirements, other than visually, and only until the user presses either "close" or "page break view" when in print preview.

sich48 · Dec 29th 2010

Re: Print Preview (Setup,Margins,*Page Break Preview*) Disable

Hi max-lux,
Kind of late reply as this thread will soon be 5 years old but I only registered yesterday. So if you're still around, here's my suggestion: if the printreview is only for error checking of visually contiguous information, a workaround could be to use a userform with an OWC spreadsheet control on it and to copy/paste your Sheet1Report into this spreadsheet. Protect the OWC spreadsheet and show this one to the user. No worries anymore of altering margins, etc. Eventually you could even allow the user to correct wrong information on it and feed these corrections back to your Sheet1Report, this of course in a way controled by code.
rgds. sich 48

Print Preview (Setup,Margins,Page Break Preview) Disable

Participate now!

Share