by Allen Wyatt
(last updated May 12, 2018)
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:
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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13526) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When printing information in a workbook, you may want to take advantage of the different print quality settings available ...Discover More
If you don't need to print an entire workbook, it can be confusing to figure out how to print just certain pages. This ...Discover More
Excel can print your worksheets in either black and white or color. If you want to print everything in black and white ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.