Microsoft Excel Hints and Tips
Question
How do I restrict my users to using only a certain range of cells within
one or more of my worksheets ?
Solution
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.
Return to Excel Exchange homepage.