Macro For Standard Print Button

Hello Juda,

Add this macro to the Worksheet where X1 will be.

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

Thanks for the response - unfortunately, the macro doesn't meet all my wishes yet - for example, with data entered on 3 pages, when I enter the number 2 into Cell X1 and hit either 'Tab' or 'Enter', the printer immediately prints out 2 pages. If I then hit the 'Print' button on the Standard Toolbar, 3 pages print. The fact that I can still use 'File/Print...' is very exciting, but the pages should not print until I hit the 'Print' button - and when I do, I only want the number of pages in Cell X1 printed.

Hello Juda,

I misunderstood your post. This macro is written to be run from a button.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross

Thanks again - but now when I enter the number '2' into Cell X1 and hit the 'Print' button on the Standard Toolbar, 3 pages still print. Also, if nothing is entered, 3 pages print as well. (Instead of none.) It's basically as if there is no macro at all.

Hello Juda,

Post you workbook and I'll review it and correct any problems with the code.

Sincerely,
Leith Ross

Here is my sample workbook. The 'Inspection Report' (Cell X1) and the 'Inspection Report Attachment' (Cell P1) are the 2 sheets that need your macro. Thanks so much for your time.

Hello Juda,

Tell me how you want this code to work. I don't see any buttons that intiate the macro. You set Cancel to True in the Workbook_BeforePrint event before you even test any conditions to determine if this action is needed. Let me know how you want to run and I will make run that way.

Sincerely,
Leith Ross

Okay, here is how I want to run:

I enter my information on the Data Entry Sheet.

I go to the Drawing Sheet and fill it out.

I go to the Inspection Report Sheet and enter 1, 2 or 3 in Cell X1 depending on the number of pages I require.

I fill out the rest of the information in the Inspection Report page(s).

I go to the Inspection Report Attachment Sheet and fill in any needed information there.

I go to the Drawing Sheet and hit the Print button on the Standard Toolbar. One page is printed using the default printer.

I go to the C of C For Kits/C of C Attachment/C of C For Parts Sheet and hit the Print button on the Standard Toolbar. One page is printed using the default printer.

I now go to the Inspection Report Sheet and hit the Print button on the Standard Toolbar. The number of pages specified in Cell X1 are printed using the default printer.

I go to the Inspection Report Attachment Sheet and hit the Print button on the Standard Toolbar. The number of pages specified in Cell P1 are printed using the default printer.

If I want to change any option, such as a different number of pages or a different printer, I go to File/Print... and choose from there.

That is how I want to run. Thanks again for your assistance. -Juda

Hello Juda,

Thank you for the detailed explanation. I have been working on the workbook code and have run it a problem. My computer is looping when it prints. I won't be able to resume work on this until I fix this printer problem.

Sincerely,
Leith Ross

Thanks so much, I'll be eagerly waiting for your reply. Have a great day, Juda

Juda,

What version of Excel are you using? I developed this in Excel 2000 but it should also work in 2003. I haven't tested it with 2007.

I did something similar to what you are trying to do by reassigning the OnAction event that happens when the print button is pressed.
You should be careful doing this because once reassigned the print button will not function normall in any open workbooks unless you reset the OnAction property of your print button.

Here is an example workbook that you can take a look at. I think this will do what you are after. I used cell C4 in my code for the cell to determine what to print.


ReassignPrintButton.xls

Thanks so much for your input, however, when I open your workbook, I receive this error:

Compile Error: Can't find project or library

I am using Excel 2003 - is that why it isn't working for me?

Try this file. I had a reference in the other from part of the my project I swiped that code from that I had removed for your application.

ReassignPrintButton.xls

Wow, I think this is the closest I've come to the solution yet. Now, do you think it is possible to incorporate this macro into the workbook I posted? I am not sure how to assign this macro to two different sheets - since the 'Inspection Report' Sheet uses Cell X1 to define the number of pages, and the 'Inspection Report Attachment' Sheet uses Cell P1. Also, what do you mean about that warning you gave me of the print button not functioning normally in any open workbooks unless I reset the OnAction property of my print button? Thanks again, Juda.

Try this workbook. I pasted slightly modified code from my other workbook into your workbook.

BlankSampleWorkbook(1).xls

My warning was that if you don't put some code like what I've included in the example workbook to remove the OnAction assignment when you either close this workbook or switch to another workbook then your print button will still run the macro it was reassigned to point to instead of the default Excel print command.

These two pieces of code are the ones that take care of that.
The first reassign the OnAction property of the print button.
The second resets the OnAction property of the print button to the Excel default.
The workbook I attached then calls the appropriate one of these when the workbook is activated or deactivated from a Workbook Event handler in the ThisWorkbook module.

Please Login or Register  to view this content.

Hope this helps solve your problem and answers your question.

Thank you for your efforts, but now when I try to open BlankSampleWorkbook(1).xls I get this error message seven times in succession:

File error: data may have been lost.

Then I try to print with the 'Print' button, but nothing happens, and when I exit, I get this message:

Do you want to save the changes to 'BlankSampleWorkbook(1).xls? Microsoft Office Excel recalculates formulas when opening files last saved by an earlier version of Excel.

What should I try now?

Let's try it this way.

Paste this code into your existing working workbook (or a copy of it, just in case).

Put this at the end of your regular module.

Please Login or Register  to view this content.

Then paste this into the ThisWorkbook module

Please Login or Register  to view this content.

save your workbook, close, then re-open and give it a go.

You will also have to either delete or comment out this section of code in your ThisWorkbook module.

Please Login or Register  to view this content.

HTH

Wow, I'm getting really excited. There were no error messages this time at all, and when I hit the Print button on the Standard Toolbar, the correct amount was printed from the 'Inspection Report'/'Inspection Report Attachment' Sheet. When I go to 'File/Print...', I can choose whatever options I like, and the printer prints out what I specified.

However, there are still a few bugs... I am now unable to print any of the other pages with the Print button.

Also, if I use the 'File/Print...' option on the 'Inspection Report'/'Inspection Report Attachment' Sheet - just to change the default printer, for example - (with 'All' selected in the Print range) and hit 'OK', I get all of the pages regardless of the number in Cell X1/P1. While this is fine on any of the other pages, it would be great if the 'Inspection Report'/'Inspection Report Attachment' Sheets would still use your macro if 'All' is selected - unless of course I select the 'Page(s)' option.

What are your thoughts on this?

Does anybody have any ideas? I could really use a solution. Thanks in advance, Juda

Changing the If statement in the PrintSelected subrouting will take care of the rest of the worksheets being able to print from the print button (see red below)

Please Login or Register  to view this content.

Your other request is going to take a little more looking into.

Wow, you are amazing! That works flawlessly now, as far as I can tell. Thanks a million. If and when you have the time and energy to solve the second half, I'll be ready, but no pressure. Thanks again!

'Till later, Juda

Whoops, I found an issue here. I had copied your macros to my actual workbook with good results, but now when I try to open it I get message:

This workbook contains links to other data sources. (And then a whole bunch of other stuff and then) Click Update or Don't Update.

How do I remove any links I have so that my workbook will still function and this message will not appear?

Thanks for your help, the links to another workbook have been taken care of, and the message no longer appears.

How does my second problem look yet?

If I use the 'File/Print...' option on the 'Inspection Report'/'Inspection Report Attachment' Sheet - just to change the default printer, for example - (with 'All' selected in the Print range) and hit 'OK', I get all of the pages regardless of the number in Cell X1/P1. While this is fine on any of the other pages, it would be great if the 'Inspection Report'/'Inspection Report Attachment' Sheets would still use your macro if 'All' is selected - unless of course I select the 'Page(s)' option.

I didn't have a lot of time to look into the second problem. Like I said before to get a handle on the Print dialog and it's settings is a bit more complicated. I was however able to get it to automatically set the page range from 'All' to 'From 1 to ' whatever you have on the sheet, when printing from the Inspection Report or Inspection Report Attachment sheets. I can provide you with that code if you'd like. It's not exactly what you want, i.e. if the user switches back to 'All' then it will still print all pages. But I think it will suit your purposes.

Okay, that sounds good. You can post the code if you like. Thanks!

Juda,

I haven't had a chance to FULLY test this but put the following code in 'ThisWorkbook' module.

Please Login or Register  to view this content.

You will also need to declare the variable x as Integer in the General-Declarations section of the ThisWorkbook module.

Give it a try and see if it fits your needs. If the corresponding cell, X1 or P1 is empty it will start with the From range as 1 to 0 and then give a default Excel error if you try to print telling you that you must enter a number between 1 and 32767. I did not trap this error since IMHO I think the default Excel error trap will work.

You will also need to declare the variable x as Integer in the General-Declarations section of the ThisWorkbook module.

I'm not sure how to do that. Would you mind walking me through it?

At the top of the 'ThisWorkbook' module there should the following lines:

Please Login or Register  to view this content.

I think when you sent me the workbook it already had everything except the , x as Integer
Just add that to your existing Declarations section and you should be good to go.

If the Dim response As Integer isn't there either add it as well.

Okay, wow, the 'Inspection Report' Sheet works swell. The 'Inspection Report Attachment' Sheet, however, still has 'All' selected when I choose 'File/Print...' - if there is no number in Cell P1. If Cell P1 contains a number, then it works. Can that be changed yet?

Take out the two lines in red from the code I posted earlier.

Please Login or Register  to view this content.

Thanks, but now I get this error message when Cell P1 is empty and I use 'File/Print...':

Run-time error '1004':
Unable to get the Show property of the Dialog class

What should I do now?

I am not getting the same error when trying to use "File/Print..." with cell P1 empty.
I do get that error if P1 has a space (rather than empty) or text in it.
Make sure P1 either is empty or only has a number before using "File/Print..."

Cell P1 contains a formula which I need. (It automatically enters the number of pages based on data in other cells.) Is there a way to get around this?

Give this a try:

Please Login or Register  to view this content.

Just wanted to let you know that your final advice solved all my issues. Thanks a million for all your time! -Juda