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

A Picture is Worth Thousands! Your worksheets are not limited to holding numbers and text. You can also add graphics or easily create charts based on your data. Excel Graphics and Charts, available in two versions, helps you make your graphics and charts their absolute best.

Check out Excel Graphics and Charts today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Limiting Scroll Area