Simple Printer Control

[Larry Dunn]

In our office, we use several reusable print forms. They may be printed at any station, to a number of different printers. Since the default printer is not visible, selecting a printer can be a pain. The failure to select a printer can result in inadvertent results, like sending a color printout to a non-color printer.

I have worked out a truly simple way to let the user know what printer is currently set to be used for the print job. The only thing I don't do here is remember the current default printer, so it could be reset at the end. In fact, I just now thought of it. Not too hard to add.

At any rate, here's the method I used:

I always use a header sheet to gather any information the user might want to have on the form. On that sheet, I place a command button labeled 'Select Printer,' with the following code:


Private Sub CommandButton1_Click()
MsgBox ("I will now bring up the Printer Dialog." & Chr(10) & "Select a printer, then press the Escape key.")
Application.Dialogs(xlDialogPrint).Show
ActiveSheet.Range("C1").Value = ActivePrinter
End Sub[/Coce]

As you can see, all this does is set the active printer, and put its name in the cell C1, which happens to be convenient in the sheet I was working in. Naturally, there's a second command button that performs the print job. It starts like this:

[Code]Private Sub CommandButton2_Click()
On Error GoTo AnError
ActivePrinter = ActiveSheet.Range("C1").Value
GoTo ProceedWithPrintJob
AnError:
On Error GoTo 0
MsgBox ("Printer selection failed. Select another and try again!")
Exit Sub
ProceedWithPrintJob:
End Sub

So you see, the printer name is placed at C1, and the print job uses the printer name it reads from C1. It's simple, visible to the user, and almost fun!

[mdmackillop]

I use this Userform interface (http://www.vbaexpress.com/kb/getarticle.php?kb_id=528). You can set up cloned copies of printers to print to different papers, colour etc. for the desired output.

[Larry Dunn]

Thank you, MacKillop, for the userform for printer selection. I'll probably use that sometime. I'm impressed that it works, but I'm put off by the awkwardness of having to prime the pump by putting printer names on the option buttons. I suppose that, if you had 20 printers to choose from, you might need 20 option buttons.

Also, I hate having to use code I can't understand. I had some code before, by Steven Roman, that listed printer names, but it wouldn't work on the network at work. I modified it to work to some extent, but it wasn't something I could call mine, or even get my head around.

And, of course, none of the solutions has used the technique of placing the printer name in the sheet. That makes it easy for the user to see what printer is about to receive the printout, and lets me set a recommended printer for that form.

On the other hand, it wouldn't be that hard to use the complicated code to work with data in the spreadsheet. And to place data in the spreadsheet, as well. Something to think about.

I had forgotten that the network number of a given printer may vary from station to station. There is a fairly simple way of guaranteeing selection of that printer, despite the limitation.

It is considered unfair by some, or messy, or inelegant, but it is simple and seemingly foolproof. It simply involves testing each number in turn until desired printer is selected. The code can be incorporated easily into any existing code, without modification - barring a name conflict, of course.

I'm attaching a workbook that illustrates all, including printing a phony form.

[Larry Dunn]

I found, in looking at the API coding of the user form, that GetPrinterKey will not work in a language other than English. The printers are, absurdly, pointed to a Network Number with the word "on." I found from a German site that their printers use the word "auf." So it is language-specific!

I've rewritten the GetPrinterKey function so that it will insert the proper word for English, French, and German. If a language is not listed, the user is notified, and requested to modify the function. It's the best I could do, but it seems awkward.

[Larry Dunn]

That was dumb. There's no need for International anything. The info is right there in the Active Printer name. Revised file attached.

[Larry Dunn]

Well, here it is! Full printer control, and it's pretty simple, too.

With these procedures, you can limit the printers available to users, if you need to do that, and also allow users to be sure what printer is selected before sending the data to print. And all from the simple idea:

ActiveCell.Value = ActivePrinter