Microsoft Office Tutorials and References
In Depth Information
Using worksheet protection
After performing these steps, you cannot activate any cell outside the specified range. Also,
some commands no longer work. For example, you cannot select entire rows and columns. Note
that the scroll area is limited to a single contiguous range of cells.
There’s a problem: The ScrollArea property isn’t persistent. In other words, if you save your
file, close it, and then open it again, the ScrollArea is reset, and you’re free to select any cell
you like. One solution is to write a simple VBA macro that is executed when the workbook is
opened. To add this type of macro, follow these instructions:
1. Make sure that the workbook window is not maximized.
One way to do this is to press Ctrl+F5.
2. Right-click the workbook’s title bar and choose View Code from the shortcut menu.
This action displays the ThisWorkbook code module for the workbook.
3. Enter the following VBA code in the ThisWorkbook code module (adjusting the sheet
name and range address, of course):
Private Sub Workbook_Open()
Worksheets(“Sheet1”).ScrollArea = “C6:F13”
End Sub
4. Press Alt+F11 to return to Excel.
5. Save the workbook, close it, and reopen it.
If your workbook has an XLSX extension, you need to save it as a macro-enabled
workbook (with an XLSM extension).
When the workbook is opened, the Workbook_Open procedure is executed automatically, and
the ScrollArea property is set.
This method is by no means a foolproof way to prevent users from accessing parts of
a workbook. Nothing can prevent a savvy user from using the Properties window to
delete the contents of the ScrollArea field. Or, when the workbook is open, the
user can choose to disable macros for the workbook. Another way to bypass the
Workbook_Open macro is to press Shift while the file opens.
Using worksheet protection
The second method of limiting the usable area of a worksheet relies on unlocking cells and
protecting the workbook:
1. Select all cells that you want to be accessible.
They can be single cells or any number of ranges.
2. Press Ctrl+1 to display the Format Cells dialog box.
Search JabSto ::

Custom Search