Microsoft Excel Hints and Tips

How do I restrict my users to using only a certain range of cells within one or more of my worksheets ?

Navigate to the worksheet you wish to restrict and then press Alt + F11. The Visual Basic Editor is now displayed. If the Properties dialog box is not displayed, then press F4 to display it. One of the Properties within the dialog box is called ScrollArea - the range that you enter into here will be range that the user is restricted to using.

Tip - Another way to get into the Visual Basic Editor is to right click on the worksheet name tab and then and select view code.

Caution - The user can still navigate to and alter a cell outside the restricted area by typing the cell reference into the Name Box located above cell A1.

Big Problem - When you re-open the workbook, even after you have saved it - the ScrollArea restriction that you have just set disappears.  To solve this problem you need to enter a piece of VBA code that will set the restriction the moment the workbook is opened.

Lets say you want to restrict the range A1:B20 in worksheet 1.  Open VBA and then double click on ThisWorkbook within Project Explorer (Ctrl + R) and then type the following VBA code:

Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:B20"
End Sub

Then click Save.
You will need to Enable Macros when you re-open the spreadsheet.

Back to Questions Index

Return to Excel Exchange homepage.