Run macro AFTER worksheet has printed

Posted by JAF on December 21, 1999 8:48 AM

I have a macro that increments a reference number on a spreadsheet which I did have attached to a print macro so that the worksheet printed and then increased the reference number by one.

Unfortunately, some of my users seem incapable of clicking the macro button and simply click the toolbar button instead resulting in 2 (or more) sheets being printed with the same reference number.

What I need is some VBA command to run the macro AFTER the file has been printed - something similar to Workbook_BeforePrint which of course increases the number too soon. The number HAS to increase AFTER the worksheet has printed.

Any suggestions or am I better off slapping my users round the head with a kipper until they get the hang of clicking a macro button?!?

Posted by Chris on December 21, 1999 10:13 AM

How about it you use the application.ontime method in the beforeprint event to kick off a macro a second or two later that increases your reference number? I've never tried it but I suppose it would work.

Chris

Posted by Ivan Moala on December 22, 1999 2:51 AM

JAF
Why not disable printing IF they use the buttons
or any of the commandbar commands ??
Try putting this routine in which will prompt the
user, telling them to use your button.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If TypeName(Application.Caller) = "Error" Then
MsgBox "Use the macro button!!"
Cancel = True
End If
End Sub

All this does is to detect wheather they have clicked the print button or other
and if so prompt them to use the macro button
and then cancel the print.

Ivan

Posted by JAF on December 22, 1999 3:31 AM

Thanks - this works nicely (as did the idea from Chris), but to be honest (and sarcastic!), I prefer the kipper option!