[Excel VBA] How to Restrict/Release Scrollable Areas | Using ScrollArea

Introduction

In Excel, there are times when you want to restrict the range of cells a user can interact with. For example, you might want to prevent accidental inputs or fix the layout of a form template.

In such cases, the ScrollArea property in VBA is very convenient. By using this property, you can limit the range of cells that can be scrolled to or selected on the screen.

Basic Code: Restricting the Scrollable Area

Sub SetScrollArea()
    ' Set the scrollable area to B5:X100
    Worksheets(1).ScrollArea = "B5:X100"
    
    ' Move the view to the top-left of the allowed area
    Application.Goto Range("B5"), True
End Sub

Explanation

• ScrollArea: A property used to restrict scrolling and cursor movement within a specified range.
• Effect: Users cannot click, select, or move to cells outside of “B5:X100”.
• Application.Goto: Used here to force the view to the beginning of the restricted area immediately.

Important Note: It Is a Temporary Setting

The ScrollArea setting is not saved when you save the workbook.

It resets every time Excel is restarted.

To make the setting persistent, you must write the code in the Workbook_Open event.

Private Sub Workbook_Open()
    ' Automatically set when the file opens
    Worksheets("InputSheet").ScrollArea = "B5:X100"
End Sub

How to Release the Restriction

To remove the restriction, simply assign an empty string to the property.

Sub ClearScrollArea()
    Worksheets(1).ScrollArea = ""
End Sub

This allows the user to navigate the entire sheet again.

Application Example: Setting Limits on Multiple Sheets

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.ScrollArea = "C3:T50"
Next

This loop allows you to set the scroll range for all sheets in the workbook at once.

Use Cases and Benefits

Summary

Using ScrollArea allows you to flexibly control the range of operations, preventing user errors and layout issues. It is a useful technique for improving security and usability in practical Excel tasks.