add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Data security is a critical issue in all organizations, as they need to ensure that data is kept confidential both internally and externally. Consequently, it is essential that you are able to prevent intentional or inadvertent deletion of data by other users and that you be able to protect data, formulas and calculations that you worked hard to prepare. These are only some of the reasons why there is a real need for securing and protecting data.
This chapter reviews all of the various options you can use to protect your files and the data they contain.
Security
New
In 2002
In Excel 2002, a Security tab has been added to the Options dialog box. It contains
a range of options that will help you secure and protect workbooks and the data
they contain.
From the Tools menu, select Options, and then select the Security tab.
Security Tab Options
Password to open
By assigning a password to a workbook, you can prevent it from being opened by unauthorized users.
Advanced
Click Advanced, and select Office 97/2000 Compatible. This option allows you to send a password-protected Excel 2002 file to users of earlier Excel versions.
Digital signature
Adding a digital signature ensures a higher level of security when working with Excel files on a network.
Macro security
The Macro security option allows you to adjust the security level for scanning of files that might contain macro viruses.
Using a password to prevent opening a workbook
In Excel 2002, you can prevent the opening of a workbook by adding a password in the Security tab, as explained below. In all Excel versions, you can still use a password to prevent opening a workbook.
1. From the File menu, select Save as.
2. In Excel 97, select Options. In Excel versions 2000 and 2002, select Tools, Options.
3. Type the password twice, and click OK.
Protecting Workbooks
Protecting a workbook prevents the structure from being changed. By assigning a password to a workbook, you prevent sheets from being deleted, new sheets from being inserted, and hidden sheets from being opened.
From the Tools menu, select Protection, Protect Workbook. Type a password in the Password box, and click OK. Now confirm the password, and click OK again.
Unprotecting a workbook
From the Tools menu, select Protection, Unprotect Workbook. Type the password in the Password box, and click OK.
Hiding sheets
Excel allows you to hide sheets (one sheet must remain visible). Hiding a sheet or sheets lets you to prevent others from viewing and/or changing data or formulas.
Select the sheet you want to hide. Then, from the Format menu, select Sheet, Hide.
Unhiding sheets
If a workbook is protected, you need to unprotect it before you can unhide a sheet. From the Format menu, select Sheet, Unhide. Now select the sheet you want to unhide, and click OK.
Protecting Sheets/Cells
You can protect the content of cells from being changed and/or hide the formulas of cells from being viewed.
Conditions for protecting cells
© The cell must be locked (in the Format Cells dialog box).
© The sheet must be protected.
Conditions for hiding text/formula in the Formula bar
© The cell must be hidden (in the Format Cells dialog box).
© The sheet must be protected.
Locking/hiding a cell
Select a cell in the sheet, and press Ctrl+1. Select the Protection tab. By default, Excel automatically checks the Locked box and does not check the Hidden box. If you will be protecting your worksheet, remember to unlock cells that you wish to be unprotected.
Protecting a sheet
From the Tools menu, select Protection, Protect Sheet.
New
In 2002
In Excel 2002, the Protect Sheet dialog box allows you to select the operations
you want users to be able to perform.
Under Allow all users of this worksheet to, select the checkboxes for the operations you want to leave unprotected.
In previous versions of Excel, all of the operations appearing in the Protect Sheet dialog box were automatically protected, and you could not select them individually.
Moving between unprotected cells in a protected sheet
Move between unprotected cells in a protected sheet by pressing the Tab key.
Protecting Data by Hiding Rows and Columns
The range of movement in the sheet displayed in the picture below is A1:E14.
The columns from F forward and the rows from 15 downward are hidden. Consequently, the area that you can move in is restricted to A1:E14.
1. Select Column F.
2. Press Ctrl+Shift+Right Arrow.
3. Right-click, and from the shortcut menu, select Hide.
4. Select Row 15.
5. Press Ctrl+Shift+Down Arrow.
6. Right-click, and from the shortcut menu, select Hide.
Unhiding rows and columns
1. Select Row 14, and while pointing the cursor at the row number, click and drag it slightly downward.
2. Right-click, and from the shortcut menu, select Unhide.
3. Repeat this technique and unhide columns, as required.
Preventing Movement in Protected Areas
You can divide the area of a sheet containing data into two parts: an area where movement is unrestricted (scroll area) and an area where movement is restricted, meaning that it is protected.
You can set the scroll area in a sheet either by using the Scroll Area macro or by making a change in the sheet’s Properties dialog box.
Changing properties in the Properties dialog box
To change the sheet’s properties, you need to open the macro editor, VBE.
1. Press Alt+F11.
2. Under VBA Project, select the sheet for which you want to change the Scroll Area property (You may need to hit Ctrl+R to display VBA Project).
3. Under Properties, select the Scroll Area cell (see the picture), and type the reference of the range you want to set as the scroll area. In the figure, note that the reference given is $A$1:$C$15.
4. To close the VBE or return to Excel, press Alt+Q.
Note
If you can’t see the Properties dialog box, hit F4 in the VBE.
The result: in the range A1:C15, you can perform any action in the cells. In all the other cells in the sheet, you are restricted to viewing the cells and cannot move or scroll between them.
Using the VBA macro statements
The statement below, for example, sets the scroll area so that the user cannot activate any cells outside it:
To set scrolling back to normal, use a statement like the one below:
Protecting Cells That Contain Formulas or Text
See more detailed information in Chapters 2, Text and Chapter 7, Formulas.
Allowing Multiple Users to Edit Ranges
New
In 2002
From the Tools menu, select Protection, Allow Users to Edit Ranges.
This advanced option allows multiple users (when working on a network for example) to update data in a well-defined and private area. Each user of the workbook is allotted a range in the sheet with a unique password.
Forgotten the Password? There’s a Solution
Even if you have forgotten your password, do not despair. In exchange for a fee, software manufacturers will be happy to provide you with a password identification program that will also cancel password protection.
Go to the Internet, and type the words Excel password into any search engine. LostPasswords.com provides such a utility. For more information, point your browser to http://ref.lostpassword.com/?118812.