Prevent Users from Performing Certain Actions

Although Excel provides overall protection for workbooks and worksheets, this blunt instrument doesn't provide limited privileges to users-unless you do some hacking.

You can manage user interactions with your spreadsheets by monitoring and responding to events. Events, as the term suggests, are actions that occur as you work with your workbooks and worksheets. Some of the more common events include opening a workbook, saving it, and closing it when you're through. You can tell Excel to run some Visual Basic code automatically when any one of these events is triggered.

Users can bypass all these protections by disabling macros entirely. If their security is set to Medium, they'll be notified of macros in the workbook upon opening it and will be offered the opportunity to turn them off. A security setting of High will simply turn them off automatically. On the other hand, if using the spreadsheet requires the use of macros, users might be more likely to have macros turned on. These hacks are a convenience and do not provide heavy-duty data security.

Preventing Save As... in a Workbook

You can specify that any workbook be saved as read-only by checking the "Read-only recommended" checkbox in the File Save options. 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.

Before trying this at home, be sure to save your workbook first. Putting this code into place without having saved will prevent your workbook from ever saving.

To insert the code, open your workbook, right-click the Excel icon immediately to the left of the File item on the worksheet menu bar, and select View Code, as shown in Figure 1-6.

Figure 1-6. Quick access menu to the private module for the workbook object

This shortcut isn't available on the Mac. You'll have to open the Visual Basic Editor (VBE) by pressing Option-F11, or by selecting Tools Macro Visual Basic Editor. Once you're there, Ctrl-click or right-click This Workbook in the Projects window.

Type the following code into the VBE, as shown in Figure 1-7, and then press Alt/-Q to get back to Excel proper.

Figure 1-7. Code once it's entered into the private module (ThisWorkbook)

Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean,[RETURN]

     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 File Save and your workbook will save as expected. Select File Save As..., however, and you'll be informed that you're not allowed to save this workbook under any other filename.

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/-Q when you're done entering the code to save it and get back to Excel. 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 start hassling 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.

Preventing Users from Inserting More Worksheets

Excel lets you protect a workbook's structure so that users cannot delete worksheets, rearrange the order in which they appear, rename them, and so forth. Sometimes, though, you want to prevent just the addition of more worksheets, while still allowing other structural alterations.

The following code will get the job done:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

 Application.DisplayAlerts = False

    MsgBox "Sorry, you cannot add any more sheets to this workbook",_ vbInformation

    Sh.Delete

 Application.DisplayAlerts = True

End Sub

The code first displays the message box with the message and then immediately deletes the newly added sheet when the user clicks OK from the message box. The use of Application.DisplayAlerts = False stops the standard Excel warning that asks users if they really want to delete the sheet. With this in place, users will be unable to add more worksheets to the workbook.

Another way to prevent users from adding worksheets is to select Tools Protection Protect Workbook..., ensure that the Structure checkbox is checked, and click OK. However, as mentioned at the beginning of this tutorial, Excel's worksheet protection is a rather blunt instrument and will prevent many other Excel features from working as well.