Microsoft Office Tutorials and References
In Depth Information
Try It
8.
In this example, you have confidential data on Sheet3 only, so instruct Excel that it’s okay to
print anything on any worksheet other than Sheet3:
If ActiveSheet.Name <> “Sheet3” Then Exit Sub
9.
Invoke the Cancel argument to halt the print process when an attempt is made to print
Sheet3:
Cancel = True
10.
Disable events because you actually will be printing something, but you don’t want to
retrigger the BeforePrint event while you are already in it:
Application.EnableEvents = False
11.
Your confidential data resides in range B5:D12. Temporarily format that range with three
semicolons to make those cells unable to display their contents:
Range(“B5:D12”).NumberFormat = “;;;”
12.
Print the worksheet:
ActiveSheet.PrintOut
13.
Restore the General format to the confidential range so the cells will be able to show their
contents after the print job:
Range(“B5:D12”).NumberFormat = “General”
14.
Enable events again, now that the print job has been executed:
Application.EnableEvents = True
15.
When completed, the entire procedure will look like this, with comments that have been
added to explain each step:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
‘You have confidential data on Sheet3 only,
‘so any other sheet is OK to print anything.
If ActiveSheet.Name <> “Sheet3” Then Exit Sub
‘Invoke the Cancel argument to halt the print process.
Cancel = True
‘Disable events because you actually will print something
‘but you don’t want the BeforePrint event to kick in.
Application.EnableEvents = False
‘Your confidential data resides in range B5:D12.
‘Temporarily format that range with three semicolons
‘to make those cells unable to display their contents.
Range(“B5:D12”).NumberFormat = “;;;”
‘Print the worksheet.
Search JabSto ::




Custom Search