Lock Print Setting
 

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?

Lock Print Setting
 

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 wrote:

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?

--

Dave Peterson

Lock Print Setting
 

"Ted" wrote in message
...
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?

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, Befo=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

Lock Print Setting
 

"Ted" wrote in message
...
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?
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