Written by Allen Wyatt (last updated February 24, 2024)
This tip applies to Excel 97, 2000, 2002, and 2003
If you are creating workbooks to be used by others, you may be interested in disabling the print options (menu and toolbar) whenever a specific workbook is open. The easiest way to do so is to use the Auto_Open macro (which runs immediately upon opening the workbook that contains it) to make the menu and toolbar printing commands no longer available. The following will do the trick nicely:
Sub Auto_Open() 'Prevent Printing via menu MenuBars(xlWorksheet).Menus("File").MenuItems("Print...").Delete 'Turn off Print icon wherever it may be in the toolbars For J = 1 To Toolbars.Count For K = 1 To Toolbars(J).ToolbarButtons.Count If Toolbars(J).ToolbarButtons(K).Id = 2 Then Toolbars(J).ToolbarButtons(K).Enabled = False End If If Toolbars(J).ToolbarButtons(K).Id = 3 Then Toolbars(J).ToolbarButtons(K).Enabled = False End If Next K Next J End Sub
You can also create a special Auto_Close macro that restores the menus and toolbars when the workbook is closed:
Sub Auto_Close() 'Reset the menu items For Each mb In MenuBars mb.Reset Next mb 'Reset the buttons For J = 1 To Toolbars.Count For K = 1 To Toolbars(J).ToolbarButtons.Count If Toolbars(J).ToolbarButtons(K).Id = 2 Then Toolbars(J).ToolbarButtons(K).Enabled = True End If If Toolbars(J).ToolbarButtons(K).Id = 3 Then Toolbars(J).ToolbarButtons(K).Enabled = True End If Next K Next J End Sub
You should note that these macros only run when the specific workbook is opened and closed. That means that your printing capability will be unavailable as long as the workbook is open—even for any other open workbooks you may have.
Another approach is to cancel any printing before it starts. The following is a macro you can place within a workbook module:
Private Sub Workbook_BeforePrint(Cancel As Boolean) Cancel = True End Sub
Whenever someone tries to print the workbook, the process is automatically cancelled. Otherwise, the menu choices and toolbar buttons remain visible. (You could also change the macro to not only cancel but to display a message box indicating that users are not allowed to print.)
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2556) applies to Microsoft Excel 97, 2000, 2002, and 2003.
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!
Have you ever noticed that when you print something, there are times that what is printed doesn't exactly match what you ...
Discover MoreWhen setting up a worksheet for printing, you can specify that Excel repeat some of your rows at the top of each page ...
Discover MoreWant to prevent your worksheet from being printed? You may be out of luck, as a determined person may be able to find a ...
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 © 2024 Sharon Parq Associates, Inc.
Comments