Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 3 Prevent Users from Performing Certain Actions

Although Excel provides overall protection for
workbooks and worksheets, this blunt instrument
doesn't provide limited privileges to
usersunless 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 workbookand 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 hack,
Excel's worksheet protection is a rather blunt
instrument and will prevent many other Excel features from working as
well.