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 (2993) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Protecting Print Settings.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
When you print a worksheet, Excel normally prints all the pages or a consecutive series of pages that you specify. If you ...
Discover MoreHave you ever noticed that when you print something, there are times that what is printed doesn't exactly match what you ...
Discover MoreNeed to print an entire workbook? It's as easy as adding a single line of code to your macros.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments