How to execute commands AFTER printing

[BigC]

I'm using Excel 2007

I know that Excel/VBA does not have a specific AfterPrint event, but need to execute some commands after a user has printed the sheet or print-previewed it.

I have a worksheet that has some cells formatted so as to hide the formulae and results returned from the user - and the results are only revealed in a print out or Preview, by using the BeforePrint event to change the format.

However, once the printout has been produced, or ClosePrintPreview button has been clicked, I want VBA to re-format the target cells to re-hide the formulae and results.

How can this be achieved?
Thanks in advance.

 

[Tom Urtis]

If for example your worksheet is Sheet3, and your range of cells to hide the contents of is B5:D12, this will do what you want. Change the General format to whatever format you really do have in those cells.

Notice the PrintPreview method is used, so you can test the code without wasting paper. Just change PrintPreview to Printout when you have it the way you want it.

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.
ActiveSheet.PrintPreview 'change to Printout when you are ready.
 
'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"
 
'Enable events again, now that the print job has been executed.
Application.EnableEvents = True
End Sub

 

[BigC]

Thanks Tom - problem solved.
Its somewhat counter-intuitive to initially use the BeforePrint event to get control of the situation, then stop it - and then force a Printout or PrintPreview to still get what I want!

Cheers