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

Remove Some Stress at Tax Time! Doing your personal income taxes can be a royal pain. Why not make the process just a bit less stressful with our 101-question checklist. You can prepare for filing your taxes with confidence, knowing you've covered all your bases.

Check out Filing Your Income Taxes Checklist today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Limiting Scroll Area