Tuesday, October 21, 2008

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).

1 comment:

Unknown said...

Ms Excel makes tasks simpler for the individual by offering entertaining help by simply simply clicking a option that will help you will all features of the worksheet.

MS Project Training