Stop printing of worksheet after print button has been clicked [SOLVED]

[cchapman]

Hi,


I'm a new member and only just joined, but am hoping someone can help me with a request from a user of an Excel based system I have set up for work?


Basically, the system takes a text (.txt) file and displays the data in a more readable format via a userform. Also included on the userform are controls that allow users to filter the list according to specified criteria that they enter.


Via another userfom, users have a functionality to print all or some of the data on a network printer of their choice. Users have a chance to abort the printing (if they've accidentally pressed the print button on the userform for example), via a third userform which asks them to either press a continue button to continue the printing, or an abort button to abort it. This is working fine, however, someone has asked whether this functionality can be extended, which is what I am having problems with.


Basically, the users now want the ability to stop the printing at any time up to when the sheet starts printing on the network printer, ie at any time after they've pressed the continue button to confirm they wish to continue printing up to when the printer starts printing the sheet.


I've tried various methods like Application.PrintCommunication, but so far without success (the sheet still prints), so am hoping an Excel guru might have an answer to this.


Thanks in anticipation,


Chris

 

[SirJB7]

Hi, cchapman!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Once Excel print out dialog gets closed (that which displays Printing nn/mmm pages and that usually is extremely fast) you've nothing to do from Excel. Your only workarounds are:

a) Managing the printing queue from the printer software menu or hardware buttons.

b) Performing actions like removing paper trays, disconnecting power supplies, and so.


Regards!

 

[Hui]

Chris


As SirJB7 said above once a print job is started you have no control over it


Your options are limited to defining the print job before you start

This includes sorting/filtering data

ie: Sort the data, then filter to show 1 page and print at a time

Defining print ranges/pages,

ie: instead of printing A1:L10000

you could print range of 100 rows at a time


The above can all be controlled/done via VBA


You may want to have a look at some existing applications:

http://chandoo.org/forums/topic/print-macro-for-selected-sheets-from-200-worksheets

or

http://chandoo.org/wp/2011/09/14/hui%E2%80%99s-excel-report-printer/


Alternatively uploading a file and explaining what you want can be done

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[cchapman]

Hi SirJB7 and Hui,


Many thanks to you both for your speedy replies - it's much appreciated. The code already includes lines to define print ranges, but I had a feeling that what the users were requesting was not going to be possible. I will tell the users the same and they will have to accept the current solution.


Do I need to mark the post as closed or anything else?

 

[SirJB7]

Hi, cchapman!

Glad to help you, nothing more to do from your side, we'll mark this topic as solved.

Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!