Excel AfterPrint Event

[thebigcheese]

I want to flag whether a sheet in a workbook has been printed or not. Whilst Excel has a BeforePrint event, I don't believe it has an AfterPrint event.

I have a procedure invoked from a button that displays the Excel Print Preview dialog. A user can Print or Close from this dialog. Is anyone aware of any code that might enable me to detect whether the user clicked on print or close?

I would use this result to flag when the sheet has been printed. I want to use this flag to warn of duplicate printing.

 

[Loomah]

Hi
Sorry I'm not in a position to test this but if you use the BeforePrint event then you should be able to create your flag.

If your user can either print or close from the print dialog then if they choose print the BeforePrint event will fire. Put the code to create your flag in there. If tey cose cancel/close nothing will happen.
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Nikita6003]

Hiya

Perhaps you could use the BuiltInDocumentProperty

LastPrinted

I know it's not foolproof & you can't determine which part of the workbook was printed last but at least it'll give your code some indication of whether the workbook was printed in the last few minutes or not:

MsgBox "last printed: " & ThisWorkbook.BuiltinDocumentProperties("Last Print Date")

HTH

Cheers
Nikki

 

[bryanbayfield]

How about designing your own form that is called when the button that currently calls up the print preview dialogue?

You could have a RefEdit control or similar for the user to select what range they wanted to print. When they've pressed OK, presuming that your code is currently in an add-in, you could store the workbook name, path and sheet name (and perhaps the range) in a sheet in your add-in.

The next time this control button is pressed you could do a look up in the add-in sheet to see whether this range/ sheet/ book combination has been printed before.

It's a bit of a judgement call as to how much Excel functionality you want to use ...

 

[andrew299]

I have been thinking about this for a while now and could see anyway of doing it but just for fun I tried this

Sub print1()
xx = Worksheets("main&quot.PrintPreview
If xx = False Then
'do something
Else
'do something else
End If


End Sub

If I click on close in the print preview xx=false but if I go through to print then OK it comes back true. It doesn't seem to work with printout.
I know its not quite what you wanted but this may be the start of a work around.