Limiting Scroll Area

Summary: If you need to limit the cells that are accessible by the user of a worksheet, VBA can come to the rescue. This doesn't involve the use of macros, but the setting of properties. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

When putting together a worksheet for others to use, you may want to limit the cells that the user can access. One esoteric way to add limits is to use the following steps:

Right-click the sheet tab for the sheet on which you want to place a limit.
In the resulting Context menu, choose View Code. The VBA editor appears, displaying the code window for the worksheet whose tab you right-clicked.
If the Properties window is not visible, press F4.
In the Properties Window, place the insertion point in the box to the right of the Scroll Area property. (Click here to see a related figure.)
Enter the range in which you want navigation possible. For instance, if you want the user to only be able to access the cells in the range A3:D15, then enter that range.
Close the VBA Editor.

That's it; you can no longer move to or select cells outside the range you specified in step 5. The range you enter must be a contiguous range; you cannot enter a non-contiguous group of cell addresses.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2669) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Step Up and Take Control! Subscribers to ExcelTips know just how valuable a resource it is. ExcelTips Premium provides twice the number of exceptional, easy-to-understand tips every week in an ad-free newsletter, as well as substantial discounts on ExcelTips archives and e-books.

Check out ExcelTips Premium today!