Problem with Macro printing code

[Usethaschwar]

I have a vba code for a form needing to print out only some specific information. This code is a "before print" code and will change the cell content's font to the background color so the information will not appear. Everything seems to work perfectly except, for some reason, the print screen does not appear when I click print and the form prints directly to my default printer. Since many people will be using/printing this form from different computers, I need this print screen to appear so they may choose which printer to print to. Strangely enough, I am able to get to this print screen when I go to print preview first, but not when I select print directly. I have attached the document containing the code below for editing. Since I was able to find this code online, and I am very bad at vba programming, I was hoping someone could make edits in the code for me. Thanks!

[Usethaschwar]

Well I have received 2 parts of advice and they seem to each solve half the problem, but not the whole problem.

I was told to try ActiveWindow.ActiveSheet.PrintPreview. But this will open print preview whether you click print preview or click print. This means that people trying to print this form will always have to go to print preview first which will probably become annoying.

I was also told to try Application.Dialogs(xlDialogPrint).Show. This will successfully open the print screen when you click print. But, for some odd reason, the print screen will also appear when clicking print preview and after you click okay on the print screen, the print preview will open without the form printing. This solution was the closest to solving the problem, but the print preview issue is still a major hassle.

Hopefully someone can assist me with this issue. Again, as of now my code works fine besides the fact that the print option is acting like the quick print option. I need people to be able to select the printer before printing. Thanks again!

[GTO]

Hi there,

Could you SaveAs the file in .xls format and post it?

Off to bed, but for whatever reason, sounds interesting. I am sort of slammed at work (excepting one could ditch "sort of" and it would be more accurate), but would like to help/learn.

Mark

[Usethaschwar]

I don't know why it says I can't save a VB project in .xls format, but here it is anyway. Hopefully you can open everything.

[Tinbendr]

You'll have to add a userform with a list of all the printers available to a user.

Here's one example. (http://www.gmayor.com/Associate_Printer.htm)

Print to a different printer without changing default. (http://word.mvps.org/faqs/macrosvba/ChangeCurPrinter.htm)

A more in-depth example. (http://pubs.logicalexpressions.com/pub0009/lpmarticle.asp?id=183)

[Usethaschwar]

Hmm, I'm a bit confused about this. Since I'm at a large company and people at many different computers connected to different printers will be using/printing this form, I don't know the names of all the possible printers that someone would print from. I'm not sure if this is what you meant by getting a list of all the available printers.

[Rob342]

Hi

Have you tried this

Application.Dialogs(xlDialogPrinterSetup).Show

[IanFScott]

Just set the sheet up as you want then:

Application.Dialogs(xlDialogPrint).Show

This shows the print dialog and you are free to select the required printer and print.

[Usethaschwar]

In response to Rob342: I tried 2 things:
1) This code by itself brings up a printer setup screen when you click print or print preview, but it will not actually print the form. When I selected the printer I wanted from the list, it just went back to the form without printing.
2) This code followed by Sheet2.PrintOut works perfectly when you click print, but gets very confusing when you click print preview. When I clicked print preview, the print setup screen appeared, and once I selected my printer, it went to print preview. I then have to select print again, and the regular print screen appears.

In response to IanFScott: I actually was suggested that code earlier, and I commented on the issues I was having with that in a post above.

I feel like this should be a rather simple problem, since I just need the print and print preview options to work correctly. In a way I think some sort of combination of the suggested codes above should work. Essentially, the only problem is that my current code causes the print option to act as the Quick Print option by not allowing the user to select the printer or other print options (like number of copies). Any other suggestions or assistance would be greatly appreciated! Thanks for all your help so far.

[Rob342]

This routine works on excel 2003

When it shows you the printers available you highlight the printer then press ok

Dim ActivePrinter As Object

Application.Dialogs(xlDialogPrinterSetup).Show
With ActivePrinter
'ActiveWindow.SelectedSheets.PrintPreview 'shows the print preview screen
ActiveWindow.SelectedSheets.PrintOut copies:=1, Collate:=True
End With