Excel VBA Printer Function Control

[speed88bump]

I have a document I created in Excel, it is two sheets when printed. I must find a solution so when the document is printed it must be printed as a two-sided document or Excel will show an error and not allow the user to print.
There are some variables that may make this an impossible task.
The users can print to any printer and there are several. So this is where I see it being an impossible task.
Second, some users may have macros not enabled upon opening. So this can cause VBA Macros to be ignored and allow the user to print two sheets instead of one front to back.
Maybe VBA is not the right method for this task. If anyone would know it would be the bright minds of the MrExcel Forums. I look forward to hearing your thoughts and solutions.
Excel 2010

 

[Trevor_S]

1. Have you tried using the Before Print event? There are some details on msdn at: https://msdn.microsoft.com/en-us/library/office/ff195836.aspx . Presumably the event occurs after the user selects print settings but before the actual printing? In which case you could write code to set the print option to double sided, and that would overwrite anything the user had selected.

2. Could you add a worksheet that tells the user to enable macros (and provides instructions, if you think the users may not know)? Write a Workbook Open event macro that hides the new sheet and unhides all others. Write an AfterSave event macro that does the same, and a BeforeSave event macro that does the reverse. Do all the hiding/unhiding of sheets using the VeryHidden property, so that it can't be undone manually.
The save event macros will ensure that any saved version of the book only has the new sheet visible. So a user that opens it with macros disabled will only see that, and won't be able to do anything else. Users who open with macros enabled will get all the other sheets unhidden for them.

 

[speed88bump]

Trevor, Thanks for your great ideas. I did find this link http://support.microsoft.com/kb/230743 which is for duplex printing. I think I can use your second idea mixed the the thread above. The only Hang up is finding a way to code the printer name so that it utilizes the printer name of the selected printer since it could be a number of printers using it. Otherwise, I'd have to code in every possible printer name that might use it.

 

[Trevor_S]

I hadn't been aware of the link that you've posted! Although a potential issue (depending upon the way things have been set up) is that it seems to require the users to have admin rights?

But in terms of getting the printer name of the selected printer, if its possible to build any necessary code from your link into a Before Print event macro, you could use ActivePrinter (as explained in: https://msdn.microsoft.com/en-us/library/office/aa212494(v=office.11).aspx ) to return the printer name - because by this stage the user will have selected the printer to use and won't be able to change it prior to the print out starting.

Good luck!!