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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
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,341

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,002
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,033
Members
410,583
Latest member
gazz57
Top