Printer setup via VBA in Excel

Question

I am trying to assign a cell in Excel for the user to type the printer name where they want the print out to go and then use that value in the

Application.ActivePrinter = (use the cell value)

Even though I have done the programming assigning a name to the cell and using it in a variable it is giving me an error.

I have set my variable as string, text, object and variant already and it's not working.

Do you know what code should I use to be able to do this?

Answer 1

Without all the details I would guess that the user isn't entering all the information needed for the printer. On our network our printers are set up so that you need the print server, printer name and network name to get excel to print correctly. In the immediate window try this line of code to see how you need to enter the printer in the spreadsheet.

debug.Print application.ActivePrinter

You might want to help the user by supplying the server and network information (if it happens to be the same for all printers). In my office you'd use something like this:

Application.ActivePrinter = "\\printserver\" & _
                            range("printername").value & " on Ne05:"

Answer 2

Printer names are very particular - if it is not entered exactly as Windows expects it, you will get an error. (Since you did not note the specific error message in your question, I am assuming this is where the problem is; it is the most likely issue.)

What I've done in this situation is provide the user with a list of available printers. You can use this code to populate a list (called lstPrinters):

Private Sub LoadPrintersListBox()
    Dim prtLoop             As Printer
    Dim strListRowSource    As String

    For Each prtLoop In Application.Printers
        strListRowSource = strListRowSource + prtLoop.DeviceName + ";"
    Next prtLoop

    lstPrinters.RowSource = strListRowSource
End Sub

Then, you can use the user's selection to set the printer. (This code assumes a button called cmdSetPrinter is available, that the user will click once the printer is selected.)

Private Sub cmdSetPrinter_Click()
    Application.ActivePrinter = lstPrinters.Column(0)
End Sub

You can be confident that the printers in the list are named according to what the system will need, and you don't need to worry about typos.