Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Disabling Printing

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.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2556) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Related Tips:

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!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Mike    20 Jul 2015, 18:01
Does this work if macros are disabled, as is standard practice when initially opening excel files these days?
Barry    20 Apr 2015, 07:33
@Margus

The "Workbook_BeforePrint" will intercept any request to print (even if it is from a macro). So to allow a macro to print I would create a global variable, say "AllowPrinting" which is set just before your macro wants to print, and reset in the "Workbook_BeforePrint" routine:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   If AllowPrinting = False then
        Cancel = True
   End If
   AllowPrinting = False
End Sub
This should be placed in the "ThisWorkBook" code page.

Sub YourCode()
      ....... your code
      ....... your code
      AllowPrinting =True
      Worksheets("Sheet1").Printout
      ....... your code
      ....... your code
End sub
The .Printout method is using default settings but can have quite a few parameters if required.

The variable "AllowPrinting" should be declared on a "Module" page outside of any routine.

Public AllowPrinting as Boolean
Margus    20 Apr 2015, 04:35
Is there any way I could disable normal printing for all files, but users should be able to print with set macro.
Barry    20 Dec 2014, 06:06
@Vuke
I suggest the following macro which only allows "Sheet2" to be printed if it is the "active" sheet:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   if Activesheet.Name<>"Sheet2" then
        Cancel = True
   End If
End Sub

This should be placed in the "ThisWorkBook" code page, and the name of sheet2 should be substituted in the "If...." statement.
Vuke    19 Dec 2014, 09:38
I have a workbook with two worksheets, I do not want clerks to be able to print sheet 1 but to print sheet 2 only. Reply to my address.
Himanshu    07 Oct 2014, 05:17
Pls explain when their is no macro enable but still printing options are not getting enable.
John    22 Sep 2014, 11:31
Does not work. Have tried numerous suggestions and all fail to prevent printing.
Chris    16 Jul 2014, 15:59
Your variables are not defined so this will not run.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.