Protect formulas and data by hiding them
Protect formulas and data by hiding them
Tip Suppose you've created a worksheet with an area in which users enter data, and you want to protect the data and formulas in the rest of the worksheet from being corrupted by those users. One solution, which works in all spreadsheets, is to hide all of the sensitive rows and columns. In Excel, select the rows you want to hide, and choose Format--Row-- Hide. Then select the columns you want to hide, and choose Format--Column--Hide. You might also want to protect the worksheet with a password to prevent others from unhiding rows or columns. Before you do that, though, you have to unlock the cells in which users will enter data.  Lock users out of sensitive worksheet areas in Excel 97 with a subroutine that uses the ScrollArea feature Highlight those cells, select Format--Cells, click the Protection tab, make sure Locked and Hidden are not checked, and click OK. To hide cells in 1-2-3 Release 97, right-click the row or column borders and select Hide. To prevent others from unhiding those cells, choose File--Workbook Properties, click the Security tab, and check Lock Workbook. Enter a password if you like. To hide a row or column in Quattro Pro 8, select it, click the right-mouse button, and select Hide from the menu. Unfortunately, you cannot prevent users from unhiding these cells. If you're using Excel 97, you can take advantage of the Scroll-Area feature -- but this requires a Visual Basic macro. When you set the scroll area via a VBA macro, the user cannot move the cursor outside that range. To activate the scroll area whenever the worksheet is opened, press <Alt>-<F11> to open the Visual Basic Editor. Select your workbook from the Project window on the left, and double-click ThisWorkbook to display the code module. Select Workbook from the drop-down list on the left side of the module. "Open" will appear on the right, and the cursor will be between the commands Private Sub and End Sub. Enter the following command between these two lines (substituting the sheet name and range, of course):Worksheets("Sheet1").ScrollArea = "A1:J10" To turn off the ScrollArea feature so that you can edit the worksheet, create another subroutine with a statement like the following: Worksheets("Sheet1").ScrollArea = "" Execute the subroutine whenever you need to turn off the ScrollArea feature. - John Walkenbach | Category: Spreadsheet Issue: Apr 1998 |
These Web pages are produced by Australian PC World © 1997 IDG Communications