Solved: Print to Default Printer

[MissRibena]

Hi all

I have a spreadsheet that is a template held on our server for use by all staff with many different printers.

I have created one macro that will print to PDF but the problem is that if they print to PDF first, PDF writer is then set as the active printer and it won't print to their own regular printer.

I can't set one printer as the active printer because different staff have different printers. I've tried to figure out how to make excel return to the the windows system default printer but no luck so far.

Any ideas?

Thanks
Rebecca

[Ken Puls]

Hi Rebecca,

I assume that your PDF code is changing the printer on the fly, correct? If so, you should be able to use something like the following:

Sub changeprinter()
Dim sPrinterOriginal As String

sPrinterOriginal = Application.ActivePrinter

'Run PDF print code here

Application.ActivePrinter = sPrinterOriginal

End Sub

HTH,

[mdmackillop]

Hi Rebecca,
Here's a userform interface for changing printers
http://vbaexpress.com/kb/getarticle.php?kb_id=528
Regards
MD

[Ken Puls]

LOL!

I forgot that was in the KB, Malcolm. :thumb

[mdmackillop]

No problem Ken,
I think your answer is the solution here, but the userform is a useful tool. I'm sure I've another to find and list all printers as well.

[MissRibena]

Hi again
Thanks for the replies. I'm still only getting to grips with VBA.
My spreadsheet has two buttons (on the spreadsheet, not a dialogue box) that I created one is "Print", the other is "Create PDF". I got the code for both from recording macros. Each macro sets a different print area and then prints or creates a PDF.

If they click "Print" button first, everything works ok. It's only if they create the PDF first, that the print becomes a problem as Excel has not set the PDF writer as the active printer and it doesn't give an option to return to the user's default printer.

Do I have to create a userform to do this, or can I do something with my existing buttons? (really, is there not an object like activeprinter, but for defaultprinter or workstationprinter?).

Rebecca

[Ken Puls]

HI Rebecca,

No, no userform needed. Just edit your button code (evertying between the Sub/End Sub lines) to reflect this:


Dim sPrinterOriginal As String

sPrinterOriginal = Application.ActivePrinter

'the rest of your existing button code goes in here

Application.ActivePrinter = sPrinterOriginal

If this doesn't make sense, post your button code and we'll give it back to you. :)

[mdmackillop]

Here's the userform version.

[MissRibena]

Thanks everyone. I worked it out now! :)

Rebecca