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 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 fol-lowed 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. Read More..

Preventing Save As... in a Workbook

You can specify that any workbook be saved as read-only by choosing Office button ➝ ave ➝ Tools Button ➝ General Options and enabling the “Read-only recommended” heckbox (pre-2007, File ➝ Save As ➝ Tools [Optionson the Mac] ➝ General options in the Save options dialog). Doing so canprevent 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 tosave a copy of your workbook to another directory or folder with or with-out 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 particu-larly 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 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, as shown in Figure 1-6).

----------------------------------------------------------------
You might have to enable the Developer tab (not standard in
Excel 2007) by selecting Office button ➝ Excel Options ➝
Popular, checking the option “ShowDeveloper tab in the
Ribbon” and clicking OK.
----------------------------------------------------------------


----------------------------------------------------------------
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 press Alt/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.

----------------------------------------------------------------
Note that when you save a workbook in Excel 2007 and it
contains either macros or code, you will be prompted to
save your workbook as an Excel macro-enabled workbook
(*.xlsm) and will be unable to save in the standard Excel file
format (*.xlsx).
----------------------------------------------------------------
Read More..

Validate with a Validation List

Excel enables you to restrict the values a user can enter in a cell. By restricting values, you ensure that your worksheet entries are valid and that calculations based on them thereby are valid as well. During data entry, a validation list forces anyone using your worksheet to select a value from a drop-down menu rather than typing it and potentially typing the wrong information. In this way, validation lists save time and reduce errors. To create a validation list, type the values you want to include into adjacent cells in a column or row. You may want to name the range. After you type your values, use the Data Validation dialog box to assign values to your validation list. Then copy and paste your validation list into the appropriate cells by using the Paste Special Validation option. You may want to place your validation list in an out-of-the-way place on your worksheet or on a eparate worksheet.

Read More..