Limiting Printing to a Workbook from a Set Location

Written by Allen Wyatt (last updated May 4, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365


Stefano is having a problem in his company where people will often copy a workbook from the server to their own computer, make changes in the workbook, and then print the workbook. He would like to enforce that only the original workbook is allowed to be printed, so he wonders if there is a way to prohibit printing unless the workbook is the one on the server.

There is no native way to do this in Word. You could, if desired, create a macro that did the following two things:

  1. Examine the path for the current document
  2. If the path is anything except the path for your server, prohibit printing

Here's an example of such a macro. You should place this particular version in the ThisWorkbook module; it automatically runs every time the user chooses to print.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim sFile As String

    sFile = "\\Server.name\folder\path\My file name.xlsm"

    If ThisWorkbook.FullName <>  sFile Then
        MsgBox "Printing only possible with server copy"
        Cancel = True
    End If
End Sub

Note that the sFile variable is set to contain the full path to the workbook (to where the workbook should be located on the company server). If the name of the current workbook doesn't match this, then printing is disabled.

There is a potential problem with this type of macro, and it has to do with the path to the file. It is possible that the path for the server may differ on a user-by-user basis. The example macro checks for a path that includes a UNC (Universal Naming Convention), but not all paths might work this way. For instance, users might have the folder on the server mapped to a drive letter. On one system the path might be g:/myserver/myfolder/ and on another it might be z:/myserver/myfolder/. The macro cannot reliably compensate for this.

You might be able to get around this with a definitely low-tech approach. First, put a small text file (created with Notepad) into the same folder on the server as the workbook. It isn't really important what the text file contains. Then, add this macro to your ThisWorkbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim sPath As String
    Dim sFile As String

    sPath = ThisWorkbook.Path
    sFile = sPath & "\" & "TestFile.txt"

    If Dir(sFile) = "" Then
        MsgBox "Printing only possible with server copy"
        Cancel = True
    End If
End Sub

What the macro does is to determine the path of the currently open workbook. This is then added to the name of the small text file you placed in the same folder as the workbook. (In this case, I've given this file the name TestFile.txt.) The Dir command is then used to see if the file exists. If it doesn't exist, then the printing is cancelled.

A more encompassing solution might be to use what Microsoft refers to as IRM (Information Rights Management). This solution, which is well suited for use in a corporate environment, allows you to control what people can and cannot do with your documents. A good place to start learning about IRM is here:

https://support.office.com/en-us/article/information-rights-management-in-office-c7a70797-6b1e-493f-acf7-92a39b85e30c

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13526) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Editing AutoText Entries Directly

Editing AutoText entries, and particularly deleting them, can be cumbersome if you have a lot of changes to make. There ...

Discover More

Getting Audible Feedback

You can add a bit of sound to your editing tasks by turning on Word's sound capabilities. This tip shows where this ...

Discover More

Setting Up Your Printer

Word allows you to take full advantage of the capabilities of your printer. Accessing those capabilities is done through ...

Discover More

Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!

More ExcelTips (ribbon)

Showing Print Preview for the Current Page

The Print Preview feature in Excel can be quite helpful. You might think it would be more helpful, though, if it ...

Discover More

Printing More than One Copy

Need to print more than a single copy of a worksheet? You can do it easily by using the printing controls provided by Excel.

Discover More

Preventing Changes to Multiple Selected Worksheets

It is common to select a group of worksheets and then print them. When done, any edits you make may affect the entire ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven more than 3?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.