Macro 43: Limit Range Movement to a Particular Area
Excel gives you the ability to limit the range of cells that a user can scroll through. The macro we
demonstrate here is something you can easily implement today.
How it works
Excel’s ScrollArea property allows you to set the scroll area for a particular worksheet. For
instance, this statement sets the scroll area on Sheet1 so the user cannot activate any cells outside
of A1:M17.
Sheets(“Sheet1”).ScrollArea = “A1:M17”
Because this setting is not saved with a workbook, you’ll have to reset it each time the workbook is
opened. You can accomplish this by implementing this statement in the Workbook_Open event:
Private Sub Worksheet_Open()
Sheets(“Sheet1”).ScrollArea = “A1:M17″End Sub
If for some reason you need to clear the scroll area limits, you can remove the restriction with this
statement:
ActiveSheet.ScrollArea = “”
How to use it
To implement this macro, you need to copy and paste it into the Workbook_Open event code win-
dow. Placing the macro here allows it to run each time the workbook opens.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click ThisWorkbook.
4. Select the Open event in the Event drop-down list (see Figure 4-7).
5. Type or paste the code.
Figure 4-7: Type or paste your code in the Workbook Open event code window.
