How To Limit The Usable Area In An Excel Worksheet

To control or limit the scroll area of an Excel worksheet be used to either guide or restrict the user to only use areas they are required to, or you want to allow access to or allow for ease of navigation on a worksheet.

There are two ways to achieve this restriction. The difference between the two methods is dependent on whether you need a temporary or more permanent solution.

Let’s take the temporary solution first. This way is to Set The Scroll Area Property.

1. First of all you need to make sure the Developer Tab is visible in Excel. If it is not you need to enable it. You can easily do this by selecting:

  • Excel Options
  • Popular
  • Show Developer Tab

2. Select Developer

3. Controls

4. Properties

5. In the properties window enter the scroll area you want to. This has to be entered manually (typed) in. For Example a cell range of A2:D10 you need to type in A2:D10.

After you have typed in your range of cells, you can then only scroll those few cells and cannot activate anything outside that set area. This is great method if you need to limit access to a table in that range of cells.

As already stated this unfortunately this is not a persistent or permanent feature. The scroll area property is reset when you save your file, close it then reopen it. If this does not provide the solution you need there is an alternative way to set the scroll area in an Excel worksheet.

This involves a small piece of Visual Basic (VB) knowledge which I will share with you.

1. Hit ALT + F11 keys on your keyboard to open the visual basic editor in Excel

2. Click on ‘ThisWorkBook ‘in the Project VBA Window

3. You will now see the ‘ThisWorkbook ‘code module- double on it click to open it

4. Enter or copy the following code into ‘ThisWorkbook’ code module

Private Sub Workbook_Open()

Worksheets(“Sheet1”). ScrollArea = “A2:D10”

End Sub

5. Press ALT+F11 to exit out of the VB editor to return to Excel normal view

6. Save the workbook as normal and then re open it.

If your workbook has an.xlsm extension it will need to be saved as as macro enabled workbook (.xlsm) extension to allow this feature to work. This will be a permanent way to set the scroll area in your workbook until you decide to change it.

Source : http://ezinearticles.com/?How-To-Limit-The-Usable-Area-In-An-Excel-Worksheet&id=6739488

Write a Comment

Your email address will not be published. Required fields are marked *