Prevent Users from Performing Certain Actions In Excel

Preventing Save As... in a Workbook

You can specify that any workbook be saved as read-only by choosing Office button ➝ Save ➝ Tools Button ➝ General Options and enabling the “Readonly recommended” checkbox (pre-2007, File ➝ Save As ➝ Tools [Options on the Mac] ➝ General options in the Save options dialog). Doing so can prevent a user from saving any changes he might make to the file, unless he saves it with a different name and/or in a different location.

Sometimes, however, you might want to prevent users from being able to save a copy of your workbook to another directory or folder with or without a different name. In other words, you want users to be able to save on top of the existing file and not save another copy elsewhere. This is particularly handy when more than one person is saving changes to a workbook because you do not end up with a number of different copies of the same workbook, saved with the same name in different folders. The Before Save event you’ll be using has existed since Excel 97. As its name suggests, this event occurs just before a workbook is saved, enabling you to catch the user before the fact, issue a warning, and stop Excel from saving.

To insert the code, open your workbook and choose Developer ➝ Visual Basic, then select View ➝ Code, and double-click on ThisWorkbook in the Project Explorer (pre-2007, right-click the Excel icon immediately to the left of the File menu item on the worksheet menu bar, and select View Code.

Type the following code into the VBE, and pressAlt/c-Q to get back to Excel proper, then save your workbook:

Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim lReply As Long
If SaveAsUI = True Then
lReply = MsgBox("Sorry, you are not allowed to save this " & _
"workbook as another name. Do you wish to save this " & _
"workbook?", vbQuestion + vbOKCancel)
Cancel = (lReply = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If
End Sub

Give it a whirl. Select Office button ➝ Save (pre-2007, File ➝ Save) and your workbook will save as expected. However, select Office button ➝ Save As (pre-2007, File ➝ Save As...) and you’ll be informed that you’re not allowed to save this workbook under any other filename, unless you’ve disabled macros.

Preventing Users from Printing a Workbook

Perhaps you want to prevent users from printing your workbook—and probably having it end up in a recycling bin or left on a desk somewhere in plain sight. Using Excel’s Before Print event, you can stop them in their tracks. Enter the following code, as before, into the VBE:

Private Sub workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Sorry, you cannot Print from this workbook", vbInformation
End Sub

Press Alt/c-Q when you’re done entering the code to get back to Excel, then save your workbook. Now each time users try to print from this workbook, nothing will happen. The MsgBox line of code is optional, but it’s always a good idea to include it to at least inform users so that they do not hassle the IT department, saying there is a problem with their program!

If you want to prevent users from printing only particular sheets in your workbook, use this similar code instead:

Private Sub workbook_BeforePrint(Cancel As Boolean)
Select Case ActiveSheet.Name
Case "Sheet1", "Sheet2"
Cancel = True
MsgBox "Sorry, you cannot print this sheet from this workbook", _
vbInformation
End Select
End Sub

Notice you’ve specified “Sheet1” and “Sheet2” as the only cases in which printing should be stopped. Of course, these can be the names of any sheets in your workbook; to add more sheets to the list, simply type a comma followed by the sheet name in quotation marks. If you need to prevent the printing of only one sheet, supply just that one name in quotes and drop the comma.