Set VBA for default printer.

[mrattana]

To All Experts,

I need an assistance in setting up a VBA for the default printer in my office. The issue is I want my spreadsheet to be set up to wherever I open the spreadsheet at any computers in the facility. I want the sheet to be printing out directly at the printer in my office only. Is there a way to do this?

Thank you.

 

[jproffer]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.ActivePrinter = "My Printer"
End Sub

This will change the default printer until excel is closed. Which is semi annoying when you have excel open all day and forget to change it back (or don't know that it changed). And there is no "After_Print" routine to set it back to the original printer.

Oh, and that will be for any sheet in that workbook. (of course after the first one, the default printer will be "My Printer", so it won't matter.

 

[mrattana]

To JProffer,

If the printer is one of the network printers in the facility. Do I just need its name or how do I do it?

Thank you.

 

[jproffer]

It will have to be added to each computer, but after that I think you just need it's name.

Printers folder > Add a Printer (in windows XP its on the left I think. In vista it's on the top, almost looks like a menu item but not as high up.)

 

[mrattana]

Thank you so much!

 

[jerik_sean]

Hi jproffer and excel gurus,

I have the same problem but i need 2 default printers so at least if the other is not available i still have the option to select the other or automatically redirect to another specified printer. Is that possible in VBA? Thanks in advance!

 

[jproffer]

Hmmmmm...I'm not sure if you can have 2 default printers.

You MAY be able to have first choice and second choice in the Before_Print event, as above...maybe something like:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
On Error Resume Next
   Application.ActivePrinter = "My First Choice"
   Exit Sub
      Application.ActivePrinter = "My Second Choice"
End Sub

But I'm not 100% sure that choice 1 being off or not installed would be an error in VBA terms. No doubt it's an error, but I'm not sure it would trigger the "Resume Next" command.

So that's untested, mainly because I only have one printer and no network to test it on, but try it.

EDIT:
You may want to start a new thread if that don't work. It will get more views simply because it will have zero replies.

EDIT AGAIN:
That won't work because "Next" is Exit Sub, it will close and print on the default printer.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   If IsError(Application.ActivePrinter = "My First Choice") Then
      Application.ActivePrinter = "My Second Choice"
End Sub

Not sure that will work either.

 

[jerik_sean]

Thanks a lot for the reply! Actually I am working on a network environment wherein any document can be opened to other computers. My goal is to restrict my staff on available printers in their area and unable them to select other printers other than printers in their area (sound to be redundant huh). Oh and a bit of info... I'm just a newbie when it comes to VBA. Thanks!

 

[jproffer]

OK, well the code I posted would go in the Workbook Module. Right click on the Excel symbol on the extreme top, left side of the excel window and choose "View Code".

Paste that into the window that opens.

As far as restricting their available printers, that would probably be up to the network administrator, who could easily install only the printers in that user's area, then restrict their rights to add others.

I'll be able to test and play around with that code at work on monday if you can't do it before then.

 

[Joe Was]

Sub PrintToOther()
Dim originalPrinter
'Print to a different printer and go back to the original printer when done.

MsgBox "The current active printer is: " & Chr(13) & Chr(13) & _
Application.ActivePrinter

originalPrinter = Application.ActivePrinter
Application.ActivePrinter = "\\netset1print\sales_2500_dept on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\netse1print\sales_2500_dept on Ne02:", Collate:=True

MsgBox "The active printer for this job is now: " & Chr(13) & Chr(13) & _
Application.ActivePrinter

Application.ActivePrinter = originalPrinter
MsgBox "The current active printer is: " & Chr(13) & Chr(13) & _
Application.ActivePrinter

End Sub