Stop a Worksheet from Printing

IanSYP

New Member
Joined
May 19, 2005
Messages
9
Good Morning

I need to stop a user from being able to print a worksheet

I know i need to put a bit of code in the sheet but have no idea what it should look like. Somebody has mentioned a before Print Events??? but unsure on this

Any ideas

Thank you very much
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Ian

Perhaps it's the BeforePrint workbook event you want.

To access that right click the Excel icon next to File and select view code.

That should open the VBA editor and display the workbook's class module.

Now select Workbook from the left dropdown and BeforePrint from the second.

You should now see this code stub.
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub
To cancel printing just add Cancel = True.
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
     Cancel = True
End Sub
If it's a specific sheet you want to stop printing then you could use something like this.
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = ActiveSheet.Name = "Sheet1"
End Sub
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good morning IanSYP

I need to stop a user from being able to print a worksheet

One user or any user? A worksheet or an entire workbook?

This code will prevent a workbook from being printed. It can be expanded on if need be :

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Sorry - this workbook cannot be printed"
End Sub

This code needs copying into the ThisWorkbook module.

HTH

DominicB
 

IanSYP

New Member
Joined
May 19, 2005
Messages
9
Thank You

Just a quick message of thanks as that has sorted the problem i have now and the next question i was going to ask
 

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,348

ADVERTISEMENT

Remember that there's nothing to stop anyone copying data to another workbook, and printing it from there!!
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi pcc

Remember that there's nothing to stop anyone copying data to another workbook, and printing it from there
... nor to stop anyone opening the file and choosing not to run macros and printing away to their hearts content. As we all know Excel is not a secure environment and any tricks that a spreadsheet developer can implement to stop users doing things they shouldn't can be circumvented by someone determind to wreak havoc in your system.

It's fine for preventing accidental destruction, but don't trust your secrets to Excel's security...

DominicB
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016
Hi Guys

Would this not work for disabling Cut, Copy, Paste mode.

http://www.asap-utilities.com/excel-tips-detail.php?categorie=9&m=85

I have absolutely no idea on how Enable/Disable Macro thing would work other than possibly hiding all the important sheets in a Workbook_Deactivate event then have a button linked to a macro to unhide them on opening thus gettiing round the issue of the user disabling the macro's.

I know security in Excel is pretty dodgy but hopefully the users of this workbook won't want to hack it to bits.

Cheers


Dave
 

Forum statistics

Threads
1,141,427
Messages
5,706,385
Members
421,446
Latest member
rena jhon

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
Top