Get Printer Options in Excel VBA

Question

I have a macro that I want to run before and after a user prints a worksheet in a Microsoft excel 2010 document. I am using the Workbook_BeforePrint method to do this. What I wanted to know is, how do I get the printer options that the user already selected when they printed the worksheet, so I can include those when I print the document using vba? For instance, my code below only prints 1 page no matter how many pages the user selected to print. I know I can use Thisworkbook.ActiveSheet.Printout copies:=2 or something like that, but I don't know how to get how many pages the user selected. Also, I'm not sure of anything else I might need to include for the print. I don't want anything from the options that the user selected to change. This would have been a lot easier if Microsoft had included a Workbook_AfterPrint method :( . Thanks in advance!

Here is what I have so far inside the workbook_beforeprint method

If (ThisWorkbook.ActiveSheet.name = "Printable Schedule" And user.colorPrintable = True) Then

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Cancel = True

    user.colorPrintable = False
    Call updateEmployeeShifts

    ThisWorkbook.ActiveSheet.PrintOut

    user.colorPrintable = True
    Call updateEmployeeShifts

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End If

GµårÐïåñ · Accepted Answer · 2014-02-02 18:04:14Z

1

How about just using Application.Dialogs(xlDialogPrinterSetup).Show so that you can select the options you want and then print. Would that work?

answered Feb 2, 2014 at 18:04

GµårÐïåñ

2,9255 gold badges35 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jason247 Over a year ago

That worked fine, although Application.Dialogs(xlDialogPrint).Show is a little more direct.

GµårÐïåñ Over a year ago

@Jason247 yes you are right, that would be a bit more direct. Just giving you a starting point ;) glad it works for you.

Collectives™ on Stack Overflow

Get Printer Options in Excel VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related