Prevent printing of protected sheets

Craig1

Active Member
Joined
Jun 11, 2009
Messages
322
Hi Guys,
I have a small issue I'm hoping someone could help me with. I want to stop users printing copies of sheets, simply because they are protected and they cannot set print area and when they do print they get a bit of a mish mash i.e printed on 4 pages etc, but they still insist on using these sheets. There is a main sheet where they should do the printing from which when they choose their required sheet the code unprotects the sheet and prints to the correct size and shape etc. Basically what I want to do is stop the users printing sheets when they are protected, if unprotected the code will print the sheet. I have the basic stop printing code below, but can it be modified to stop printing for protected sheets only.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

As always Thanks in advance.

Craig.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Craig,

The no frills way i.e. without prompting the user to use the main print sheet....

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = ActiveSheet.ProtectContents
End Sub

Hope that helps.
 
Upvote 0
never tried this but could you set the print area on those sheets to a1, then they will get a naff print out
 
Upvote 0
Thanks Tony that worked great.
Mole999 I considered doing it as per your suggestion but when a sheet has printed from the main sheet it remembers it's last print area and that last print area might not be what they actually want to print, so making the print area A1 would only last until that sheet or part of that sheet has been printed via the main sheet.

Thanks for the help guys,

Craig.
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,490
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top