Print Formula

[swordswinger710]

When I hit the print button on the Standard Toolbar, I want my printer to print only the number of pages specified on my worksheet which I previously entered into Cell A1.

Is there a formula or something I can use for this?

[swordswinger710]

My worksheet contains three pages of information. Cell A1 at the top of my worksheet receives the number of pages on that worksheet that I want to print. Now if I enter a 2 into Cell A1, I want to be able to hit the print button on the Standard Toolbar and have only the first two pages printed. Up until now this only works if I go File/Print.../and then type in the number of pages I need and then hit OK. Otherwise, if I hit the Print button in the toolbar, I'll get three pages no matter what I've entered into Cell A1. Is there a formula for a quicker method of printing? Any help with this would be greatly appreciated.

[DCSwearingen]

Are there three printed pages on a single worksheet tab if you print the full worksheet?

If this is the case the following should work for you. I have set the print preview to true so you can see the visual of what will be printed. Change it to False if you don't want the preview.

Please Login or Register  to view this content.

• Copy the code from the above pane.
• Open the VBE window by pressing the key combination <Alt><F11>
• If the Project Explorer is not visible, press <Ctrl><R>
• From the Project Explorer (left pane on my machine), double-click 'ThisWorkbook'
• Click into the right pane
• Paste the code copied earlier

Switch back to your workbook, and with your worksheet active, click the print button. You should see the Print Preview screen and only have the first two sheets available. if all is as hoped, click on the Print utton in the preview mode and what you see should be what you get printed.

[swordswinger710]

Thanks, that works well, the only problem is I need to print the number of pages I specified in Cell A1 - either 1 page, 2 pages, or 3 pages. Is there a way to make this work?

[DCSwearingen]

Thanks, that works well, the only problem is I need to print the number of pages I specified in Cell A1 - either 1 page, 2 pages, or 3 pages. Is there a way to make this work?

The changes are in red in my previous post. Again, once you are comfortable with it and no longer want to see the Print Preview, change the True to False (in blue above.)

If Cell A1 is empty or non-numeric, everything will print.

[swordswinger710]

I did that, but now when I click on the Print button, the screen just blinks and nothing prints. What should I try now?

[DCSwearingen]

I did that, but now when I click on the Print button, the screen just blinks and nothing prints. What should I try now?

Just before I received the notice that you posted this last item, I was reviewing the code and noticed that potential.

I finally created a dummy workbook and this works on my system. I guess I should test before posting.

Please Login or Register  to view this content.

[swordswinger710]

Now when I typed the number 1 into Cell A1 and hit Print, it showed me the preview for 1 page - so far so good. But when I hit print, I got the first page, then all three pages, and then all three pages again.

[DCSwearingen]

Now when I typed the number 1 into Cell A1 and hit Print, it showed me the preview for 1 page - so far so good. But when I hit print, I got the first page, then all three pages, and then all three pages again.

Have a try at the attached file. I played around with the Cancel = True and tested several variations of values in cell A1 - Empty, Zero, One, Two, Three - and all seemed to work for me.

The Sheet1 actually has a space entered on sufficient ranges for Excel to think there is something to print.

[swordswinger710]

Wow, you blew me away! I didn't think it was possible. You deserve a 5 star rating- thanks a lot! This saves me a lot of trouble.

My boss found out that I got this working, as he's wondering if it's possible to print a workbook using a specified printer - other than the default printer. So basically, he wants to be able to hit the print button and not worry about changing the default printer every time he wants to print from that workbook. Is this possible?

[DCSwearingen]

My boss found out that I got this working, as he's wondering if it's possible to print a workbook using a specified printer - other than the default printer. So basically, he wants to be able to hit the print button and not worry about changing the default printer every time he wants to print from that workbook. Is this possible?

I am not sure on that one. I am at home, so only have one printer. I'll have a look when I get time on Monday and see what I can come up with.

As you could probably tell, I do a lot of trial and error with VBA code, so don't give me above one star.

[mdma]

 This changes the active printer to the required one, and then changes it back: 
  
Sub PrintToAnotherPrinter() 
Dim STDprinter As String 
    STDprinter = Application.ActivePrinter 
    'to change the active printer 
    Application.ActivePrinter = "newprintername"  
    'this prints the sheet to newprintername 
    ActiveSheet.PrintOut  
    'reverts printer back to orginal printer 
    Application.ActivePrinter = STDprinter  
End Sub  

there are issues over the ne: number if using a network printer, but there are also tips on how to handle that out there.

[mdma]

since posting this, i have posted a further reply on

http://www.excelforum.com/showthread...79#post1909879

[olasa]

I would try to record a macro and then go into the code and change the Number of pages to the cell reference of your choice.
//Ola

[martindwilson]

try

Please Login or Register  to view this content.

but you'll have to assign this to a new button as it wont affect the standard print one

[shg]

Threads merged.

[swordswinger710]

there are issues over the ne: number if using a network printer, but there are also tips on how to handle that out there.

Wow, thanks for all the response! My boss is using a network printer - so far when I enter your code into his worksheet and put in the printer name, nothing changes. Here's what I did:

Sub PrintToAnotherPrinter()
Dim STDprinter As String
STDprinter = Application.ActivePrinter
'to change the active printer
Application.ActivePrinter = "Phaser 8560-6 PS Color"
'this prints the sheet to newprintername
ActiveSheet.PrintOut
'reverts printer back to orginal printer
Application.ActivePrinter = STDprinter
End Sub


Anything I can do to get the correct network printer to print this workbook?

[swordswinger710]

Have a try at the attached file. I played around with the Cancel = True and tested several variations of values in cell A1 - Empty, Zero, One, Two, Three - and all seemed to work for me.

The Sheet1 actually has a space entered on sufficient ranges for Excel to think there is something to print.

Okay, now I've located a problem. My workbook has a total of seven sheets, two of which I would need your code to print the specified number. I think our problem is that we put a code into 'This Workbook', and I need codes for separate sheets. I tried to copy the code from 'This Workbook' to 'Sheet 1' and 'Sheet 2', but it doesn't work. How can I do this?

[DCSwearingen]

See Next Message

[DCSwearingen]

Wow, thanks for all the response! My boss is using a network printer - so far when I enter your code into his worksheet and put in the printer name, nothing changes. Here's what I did:

I did a quick macro record and changed my printer on our network. Bear in mind the network you need to use will be totally different. You will need to specify the exact printer using your network addressing syntax. The easiest way may be to just record a macro while switching to the target printer and grab the printer address from that recording, as I did to get my AltPrinter.

You could set up a couple of buttons to select which printer and attach them each to a macro such as below.

Please Login or Register  to view this content.

[swordswinger710]

I did a quick macro record and changed my printer on our network. Bear in mind the network you need to use will be totally different. You will need to specify the exact printer using your network addressing syntax. The easiest way may be to just record a macro while switching to the target printer and grab the printer address from that recording, as I did to get my AltPrinter.

You could set up a couple of buttons to select which printer and attach them each to a macro such as below.

Okay, how do I record a macro? Or set up some buttons? I'm sorry, but I'm quite new to all this macro stuff.

And just to clarify things a bit, we're now talking about two different workbooks - my workbook, and my boss' workbook. My boss' workbook is the one that needs to print using a network printer other than the default one, and my workbook is the one that should only print the number of pages specified in certain cells on two of the sheets (see my last post).

[DCSwearingen]

Okay, how do I record a macro? Or set up some buttons? I'm sorry, but I'm quite new to all this macro stuff.

I have been into VBA about three years now, and most of what I know, I learned from spending about a half hour a week looking at thread titles and seeing what intrigued me. Not too much later I started posting my own questions, and now I can answer many of the less complicated questions!!

First, a few basics that need to be understood.

Each revision of Excel will have some slight variations - I am using Excel XP Pro (2003)...

The Macro Recorder will record your mouse selections and keyord strokes, exactly as they are performed. This is frequently not the most optimum method of doing something, but it can give you an insight into methods and properties.

There are different kinds of modules,

• The Standard Module is where the recorder will place a recorded macro. There may be many standard modules if you record in different sessions, so be aware of that.
• The ThisWorkbook module is a special module that is usually for utilizing what are called 'Workbook Events'.
• There can be individual worksheet modules, usually for utilizing the individual worksheet events.

The VBE (Visual Basic Editor) Window is where all of these are visible. There are quite a few ways to open the VBE window - The key combination <Alt><F11>; Right-clicking on the Excel logo to the left of File in the Excel menu bar; Selecting Tools > Macro > Visual Basic Editor; Using the Visual Basic toolbar; there are probably others, but these are the methods I know of.

In the VBE window there are quite a few 'panes' that are available. For now, just stick with the Code Pane and the Project Explorer Pane. The code pane should always be visible and the project explorer can be opened and closed as you see fit. Select View on the VBE menu bar to see the options.

Double-clicking on the items in the Project explorer will determine what is in the code pane.

If you don't have the VBA Help feature installed, I would ask to have it installed as it is not installed with the routine Excel install, there is a ton of information there - far right on VBE menu, select Help, or click into one of the first words in a line and press <F1> and see if you get help or a message telling you the Help File is not installed....

To record a macro, while in Excel, select Tools > Macro > Record New Macro... You may want to give your new macro a meaningful name as it can get confusing th look for 'MacroXX' in several different modules after several sessions... No spaces or special characters!! Use CamelBackNotation such as in that long word that has a capital to start each word. You can use the underscore to separate words.

Then find the network address of the printers your boss will be using (hopefully they are available on your machine?); While the macro recorder is running, select File > Print to bring up the Print dialog. In the printer name text box dropdown select the printer. I generally click on Preview so as to save paper. Remember the recorder will record exactly what you do, whether it is truely necessary or not.

If the Stop Recording toolbar is visible, click on the small square button to stop the recorder. If it is not visible select Tools > Macro > Stop recording.

Now, go back to the VBE window to see your recorded macro!!!

Mine looks like this

Please Login or Register  to view this content.

I added the color and bold to show (blue) the address and (red) the print preview that was recorded.

I recorded similar macros several times, activating different printers and then created a set of subroutines that can be activated by buttons. Again, these are OUR network printer addresses. All these do is set the variable AltPrinter to be the text string that I found when I did the recorded macros, and then calls the main printing routine

Please Login or Register  to view this content.

Notice that after I set the text string, I then use the variable when calling the main subroutine PrintToAnotherPrinter, which requires an argument be passed to it - in magenta below.

Please Login or Register  to view this content.

To set buttons, the easiest is to make the Forms toolbar visible by selecting View > Toolbars > Forms.

• Click on the 4th icon (the one that looks like a button) and your cursor should change to crosshairs.
• Click and drag on the sheet to create a button.
• The Assign Macro dialog should be presented. At the bottom, select Macros in This Workbook to trim the list if you have macros in any other open workbook.
• Select one of your first calling routines, e.g. SetHwyPrinter or SetMgrPrinter in the above two cases.
• The button should still be in the edit mode (gray shadow box with sizing handles aroud it); edit the name to something meaningful, like "Print To Hallway Printer" or "Print to Manager's Printer" to match the macro you are assigning. You can make a few other changes to appearance while in the edit mode.
• If you happen to get out of the edit mode before you are ready, simply right click on the button to once again be in edit.

Set up as many buttons as you would like to have printer options.

Now, for your macro to select one, two, or three pages to print...

There is no Worksheet_BeforePrint event so this must be controlled by the ThisWorkbook module, but we can just use the Worksheet_BeforePrint to call a routine that prints the number of sheets you enter in cell A1.

In the ThisWorkbook module

Please Login or Register  to view this content.

And, in a standard module (can even be the same module as your boss's macros)

Please Login or Register  to view this content.

I changed the Preview to equal False in the above code.

If there is nothing (or a non-numeric value) in cell A1 of the active worksheet all pages of that sheet will be printed; otherwise the routine will use that value to set the number of pages to be printed.

You should be able to have all of this code in one workbook, regardless. Just put the various buttons on any and all of the sheets that may need to have the printing options. Again, of cell A1 is non-numeric, or empty, the entire sheet will print, just as though the before print macro was not there.

If there is a number there, even the Print Preview will invoke the routine and you will only see the number of pages as the value in Cell A1.

We could get fancy and call up an input box that asks which printer and how many pages, but that would require knowing all of the potential printers that the workbook might be opened.

[swordswinger710]

Wow, thanks for all that info. I'm now a wee bit more familiar with macros, but I still can't get them to work like I want them to. Maybe my problem is that I don't want any extra buttons, the only button I'd like to use is the Print button on the Standard toolbar. Anyways, here's the name of our network printer I found after recording a macro:

"\\Server-1\Phaser 8560-6 PS Color on Ne05:"

Can I get this to work with the normal Print button?

As for my workbook with the number of sheets to print, I need Sheet1 to print the number of pages specified in Cell X1, and Sheet2 to print the number of pages specified in Cell P1. I can get one or the other to work, but not both in the same workbook. What am I doing wrong?

[DCSwearingen]

I'd like to use is the Print button on the Standard toolbar.

I obviously cannot test this with your printer address, but I did test it with one of our network printers.

What I have done is incorporate a YesNoCancel message box.

The code does two things when the standard print button is selected.

First, it asks if the user would like to use the Phasar Color Printer. The responses are:

• Yes: The active printer is set to the address provided in your post.
• No: The active printer is the user's default printer
• Cancel: The print operation should be canceled.

Second, (If not canceled) it checks to see if there is a numerical entry in cell A1 of the active sheet. The possibilities are:

• Cell is empty or contains text: All pages of the active sheet will print to the printer selected above.
• Cell contains a number: All pages from one through the number in Cell A1 will be printed to the printer selected above.

[swordswinger710]

Workbook B

Ok, I can get the correct printer to print now, only my boss would like to be able to just click the Print button on the Standard toolbar and have it automatically print to the color printer without any pop-up window appearing. If he wants to use another printer, he would just click File, then Print, then choose whatever he likes. Your macro currently gives us the pop-up window whether we hit the Print button or go File/Print.

Workbook A

I still can't get Sheet1 to print the number of pages specified in Cell X1, and Sheet2 to print the number of pages specified in Cell P1 - I can only specify one cell or the other in the macro. Is there a way around this?

[DCSwearingen]

my boss would like to be able to just click the Print button on the Standard toolbar and have it automatically print to the color printer without any pop-up window appearing. If he wants to use another printer, he would just click File, then Print, then choose whatever he likes

I am not aware of a method of changing the action the standard toolbar printer button does. I am sure there is a solution besides what I will propose, so if anone wants to jump in, feel free.

My proposal would be to write the code to add a second print button attached to a macro to control the printing. This would then need to be removed when the workbook is closed, or no longer active.

I am a little busy right now, but if this will work for you, I can try to create the macro for you in a day or two.

[swordswinger710]

I'm still not sure if I've been clear enough. I need two different macros for two separate workbooks -

1. to print to a printer other than the default one.

2. to print the number of pages specified in Cell X1 on Sheet1, or if I'm printing Sheet2, then the number of pages specified in Cell P1.

The Print button in the Standard toolbar should be the one to trigger the macro in both cases.

Maybe it would simplify things a bit if we just figured out the first one, then moved on to the second. I hope this helps.

[DCSwearingen]

I'm still not sure if I've been clear enough. I need two different macros for two separate workbooks -
1. to print to a printer other than the default one.
2. to print the number of pages specified in Cell X1 on Sheet1, or if I'm printing Sheet2, then the number of pages specified in Cell P1.
The Print button in the Standard toolbar should be the one to trigger the macro in both cases.
Maybe it would simplify things a bit if we just figured out the first one, then moved on to the second. I hope this helps.

1. Like I said earlier, I don't personally know how to change the action of the print button on the Standard toolbar. I have a new button to print to the printer address you gave earlier.
2. The cells with the number of pages to print changed. Therefore we needed to have this set for each individual sheet.

The attached zip has two workbooks, one to create a button to print to a specific printer, the other to select the number of pages to print. I have tested both on my system, but you may need to do some tweeking, especially on the individual worksheets to set the number of pages to print.

[swordswinger710]

Great! My boss is quite impressed (he still would prefer to use just the normal Print button, but he said that the other button is fine too.)

I was able to get my worksheet printing the right amount of pages as well - the only problem being that I can't go File/Print and then change anything if I want to. It would be a great help if this worked, but I can make do without it if this is too difficult to solve. Thanks a ton for all your hard work in this.

[DCSwearingen]

Great! My boss is quite impressed (he still would prefer to use just the normal Print button, but he said that the other button is fine too.)

I was able to get my worksheet printing the right amount of pages as well - the only problem being that I can't go File/Print and then change anything if I want to. It would be a great help if this worked, but I can make do without it if this is too difficult to solve. Thanks a ton for all your hard work in this.

If you want to use the file print and change options, we could put the Yes / No / Cancel button into your version.

[swordswinger710]

If there is no possible way to make only the Print button use the macro (although I thought that in the very beginning we had a macro that sort of worked this way), then I guess we should have the Yes/No/Cancel button.

[DCSwearingen]

If there is no possible way to make only the Print button use the macro (although I thought that in the very beginning we had a macro that sort of worked this way), then I guess we should have the Yes/No/Cancel button.

The last gyration still uses the Before_Print event, it just looks for the number of pages to print from whatever cell you have designated on each worksheet.

I did need to add a Workbook_Activate event as nPages was zero unless something actually changed after opening the workbook. That would cause the error handler to kick in and just print all pages.

Now it will set nPages on opening. If there is not a valid number returned it will still go to Print Preview, once you are in the print preview mode you can change all the parameters from there.

[swordswinger710]

Sorry, but I'm not sure I understand what you just did.

[DCSwearingen]

Sorry, but I'm not sure I understand what you just did.

First - Open the VBE window.
In the code window you will see a thin gray border on the left - about 1/4" wide on my system. If you click in that strip, next to a line of code that has an action, there should be a brown dot left there, with the entire line highlighted. If you click the brown dot, it will go away.

This brown dot pauses the program before that line of code is executed. Pressing the <F8> key will then step through the program.

Make sure the Immediate window and the Locals window are visible by selecting them under the View menu item.

I placed a pause on the line

Please Login or Register  to view this content.

in the Workbook_BeforePrint event.

When I started the print process the execution paused at that point.

Since I declared nPages as a public variable in the regular module, I had to ask what the value was. So in the immediate window I typed

Please Login or Register  to view this content.

- Remember the Question Mark goes at the beginning to ask what value a variable holds.

I found that the value was always zero unless somethins was actually changed after opening the workbook.

For this reason, I utilized the Workbook_Activate event (Would probably have been better to use the Workbook_Open event now that I think about it) but this did work...

When the workbook is activated, the Workbook_Activate event makes sure that Sheet1 is the active sheet and also makes sure the value in Cell A1 of Sheet1 is held in the public variable nPages.

That way, if no change is actually made to the workbook after it has been opened, attempting to print will bring up the print preview window with the number in cell A1 being the number of pages previewed. If cell A1 happens to be empty or zero, the entire worksheet will be previewed.

[swordswinger710]

Ok, now I see. It works great.

So is there no way to assign our macro to only the 'Print' button (and not the File/Print... method)?

[DCSwearingen]

So is there no way to assign our macro to only the 'Print' button (and not the File/Print... method)?

I think there may be, but I am not one of the terue experts here, and I don't know how to do it. The real experts seldom read posts that have multiple replies unless they were involved early in the thread.

Try starting a new post - Reference this one in the body of the post by copying and pasting the address at the top of the bropwser window!!!

In the header, use a descriptive phrase such as "Change behavior of a built-in toolbar button."

Good luck.

[MrJennings]

removed to a fresh post