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.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
When you print multiple copies of worksheets that require more than one page each, you'll probably want those copies ...
Discover MoreHave you ever noticed that when you print something, there are times that what is printed doesn't exactly match what you ...
Discover MoreWhen you print out a filtered worksheet, you may want some sort of printed record as to what filtering was applied to the ...
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