Stop a Worksheet from Printing

[IanSYP]

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

 

[Norie]

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.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

To cancel printing just add Cancel = True.

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.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = ActiveSheet.Name = "Sheet1"
End Sub

 

[DominicB]

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 :

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]

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]

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

 

[DominicB]

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]

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