Halt Printing

[EssoExplJoe]

I am printing a lot of data from an excel spreadsheet using a vba macro print loop for printing many forms. I want to allow the user to cancel printing in the middle of the job but can't find a way to accept the user's clicking on a cancel button. I have tried DoEvents but this seems to just be ignored by VBA. Is there any way to let the user interact with code running in a vba macro?

[shg]

You could put up a modeless userform with a Cancel button that clears a public variable that is checked by the print code in each iteration.

[EssoExplJoe]

Tried that but the cancel click event code is not executed to clear the public variable until the print macro is finished even though I have DoEvents in the macro. Also tried running from a modal form with same result..the events are not executed until the macro is done.

[shg]

Tried that but the cancel click event code is not executed to clear the public variable until the print macro is finished

Can't imagine why it shouldn't. It's always worked for me.

Also tried running from a modal form with same result.

With a modal form, code suspends (other than form events) until the form is closed -- that's why you need a modeless form.

[LJMetzger]

It looks like you have two issues:
a. Stopping the Macro - which shg has addressed.
b. Clearing jobs from the Print Spooler. You may be able to do this as follows (if you have Administrator privileges):
(1) Install file 'ClearPrintQueue.bat' in the same folder as your Excel File.
(2) Run Macro SpawnClearPrintQueueDotBatFileAsAdministrator() from your Excel file.

Unfortunately with today's fast printers with lots of memory, the printer itself may have control of the printing process.


Contents of file 'ClearPrintQueue.bat':

Please Login or Register  to view this content.

Run Macro SpawnClearPrintQueueDotBatFileAsAdministrator() to clear the Print Queue. Windows will ask you if you want to Continue:

Please Login or Register  to view this content.

NOTE: Not allowed to upload .bat file - If you download attached file, change the file name to 'ClearPrintQueue.bat'.

Lewis

[EssoExplJoe]

Thanks...I'll give it a try. My printing is actually done to a word document which is then printed. If I can get user events to be recognized during the generation of the word document (this takes a couple of minutes to generate), then the user can stop the word document and I wouldn't send to the printer.