Sharing an Excel workbook with a group also means being involved with different printers, different PCs and different user requirements and expectations. This is nowhere more apparent then when it comes to printing a worksheet. Different users obviously have different PCs and may have different printers, so the printed results can vary from one user to another. In addition, different users may change the print ranges in what is produced from a worksheet.
If you are responsible for a particular worksheet, you may want to somehow protect the various print settings you've established so that they aren't garbled by other users. Perhaps the easiest way to do this is to save your print settings in a macro, and then run that macro every time the workbook is closed. In that way, the settings can be changed back to the "defaults" you specify, without worry that users will mess them all up.
For instance, the following macro shows how you can set all the print settings for a particular print job:
Sub Auto_Close() With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(1) .RightMargin = Application.InchesToPoints(1) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 99 .PrintErrors = xlPrintErrorsDisplayed .PrintArea = "MyPrintArea" .PrintTitleRows = "" .PrintTitleColumns = "" End With End Sub
To make the macro work for your particular needs, simply modify the settings to match whatever your requirements are.
Of course, when someone else opens your workbook, the macro may be disabled automatically or they may see a notification that there are macros in it. If they choose to disable the macros, then your default-setting macro won't run when the workbook is closed. The solution, of course, is for you to open the workbook, enable the macros, and then close the workbook. This runs the macro and your settings are again restored as you want them.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11599) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Protecting Print Settings.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Need to print just a portion of a worksheet? It's easy to do if you follow the steps in this tip.
Discover MoreIt is not uncommon to use Excel to print out regular reports. If your report needs to span multiple worksheets, here's ...
Discover MoreIf you are using a macro to create your printed Excel output, you may need a way to specify that paper should come from a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-04-03 14:02:48
Graham
Rather than manually creating this macro, which can introduce errors or omissions, there is an easy alternative.
1) Start recording a macro
2) Go into Page Setup (no need to make any changes)
3) Exit Page Setup
4) Stop recording the macro
This should create a macro containing ALL of the current print settings. I have found this to work from Excel 97 to Excel 2010.
If it does not work, then whilst recording the macro, try changing a single parameter and immediately change it back to its original value.
2017-03-18 17:10:03
David Gray
Another option is to store the settings in custom document properties, write a generic macro that reads and applies them, and save the macro as an add-in. This option makes it possible to apply them to any workbook, without embedding macros in it. Applying the settings can then be just a hot key away.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments