Restricting the Scroll Area in Excel

Non-VBA and VBA solution

If you need to limit where a user can scroll to in a sheet you can change a setting in the VBA screen to restrict access to a specific range.

Press Alt + F11, this opens the VBA window, this technique doesn’t involve any VBA, but you can still use VBA to make it easier if you want.

On the left of the screen you should see a list of the sheets in the file. Click on the sheet where you want to restrict access and press the F4 function key. This opens the Properties window for the sheet.

If you look down the list of options you will notice the ScrollArea. See image below.

Scroll area

In the ScrollArea box type the range for the scroll area eg A1:Z100 this will only allow the user to scroll to cells in the range A1 down and across to Z100. Excel will automatically change the reference to a fixed reference $A$1:$Z$100. Click the red cross at top right corner of the VBA window to close it.

This scroll restriction applies to all users. If you need to change the setting it may be worth using a macro to apply and remove the restriction.

The macro below will clear the ScrollArea in the current sheet.

Sub Remove_Scroll
Activesheet.ScrollArea = ""
End Sub

The macro below will set the ScrollArea in the current sheet.

Sub Set_Scroll
Activesheet.ScrollArea = "$A$1:$Z$100"
End Sub

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

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