Lock Print Setting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a template document at work where a team of 15 use it to
complete accounting reconciliations. However, when it is printed out each
person has access to modify the print setting and resize the doucment in
various forms. So I was wondering if there is a way to lock this?
 
Does this mean you want to stop the users from adjust the page layout?

If yes, then maybe you can tie into the workbook_beforeprint event and just set
everything the way you want--essentially overriding any change the user makes.

If no, you may want to rephrase the question.
 
Ted said:
I have created a template document at work where a team of 15 use it to
complete accounting reconciliations. However, when it is printed out each
person has access to modify the print setting and resize the doucment in
various forms. So I was wondering if there is a way to lock this?
Click to expand...

This might help. This code should disable all the standard print options.

Set up to print the way you want, then save your file.
Add this code to ThisWorkbook in the VB Editor:

Private Sub Workbook_Activate()
'Disable standard print options & Add Print to Command Bar
Dim MenuObject As CommandBarPopup
'Add Print to Commandbar
posEdit = Application.CommandBars(1).Controls("Edit").Index
Set MenuObject = Application.CommandBars(1).Controls _
..Add(Type:=msoControlPopup, Before:=posEdit, Temporary:=True)
MenuObject.Caption = "&Print"

On Error Resume Next
With Application
'Disable Shortcut Key
.OnKey "^p", ""
'Disable Print Options
.CommandBars("File").FindControl(Id:=4).Enabled = False
.CommandBars("File").FindControl(Id:=247).Enabled = False
.CommandBars("Print Area").FindControl(Id:=364).Enabled = False
.CommandBars("Print Area").FindControl(Id:=1584).Enabled = False
End With

Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.Caption = "Print This"
MenuItem.OnAction = "PrintNow"

End Sub

Private Sub Workbook_Deactivate()
Dim MenuObject As CommandBarPopup
'Delete Print from CommandBar
Set MenuObject = Application.CommandBars(1).Controls("Print")
MenuObject.Delete

On Error Resume Next
With Application
'Enable Shortcut Key
.OnKey "^p"
'Enable Print Options
.CommandBars("File").FindControl(Id:=4).Enabled = True
.CommandBars("File").FindControl(Id:=247).Enabled = True
.CommandBars("Print Area").FindControl(Id:=364).Enabled = True
.CommandBars("Print Area").FindControl(Id:=1584).Enabled = True
End With
End Sub

Add a Module to project with this code:

Sub PrintNow()
ActiveWorkbook.PrintOut
End Sub

HTH Jay
 
Ted said:
I have created a template document at work where a team of 15 use it to
complete accounting reconciliations. However, when it is printed out each
person has access to modify the print setting and resize the doucment in
various forms. So I was wondering if there is a way to lock this?
Click to expand...
Ted,
There was an important oversight in my recent posting on this subject.
After you have added the code to the workbook, you must rem out the
following code in the workbook deactivate event.

'Set MenuObject = Application.CommandBars(1).Controls("Print")
'MenuObject.Delete

It will try to delete a control that is not there yet.
Save the file, close, and then reopen. You can then go into the editor and
remove the ' from those lines. Save again, close, then reopen and all
should work.

Sorry,
Jay
 
Back
Top