Prevent Scrolling on Excel Worksheet

Even if a worksheet is protected, you are able to scroll in any direction, and see what is on the sheet. To prevent scrolling on Excel worksheet, change the ScrollArea property for the worksheet, either manually or with a macro.

Not a Security Feature

Like other Excel protection settings, this is just a deterrent, or a navigation aid, rather than a security feature. Don’t depend on the ScrollArea setting to guard your worksheet!

NOTE: The Scroll Area has to be set each time the workbook opens. To automate this, use a macro that runs when the workbook opens. There is sample code below.

Set the Scroll Area Manually

To see how the Scroll Area setting works, you can enter the range address manually.

To manually set the Scroll Area:

• On the Ribbon, click the Developer tab, and click Properties

• In the Properties window, enter a range address in the ScrollArea property box

• Click on the worksheet, and you will be restricted to clicking and scrolling in the Scroll Area.

To manually clear the Scroll Area:

1. On the Ribbon, click the Developer tab, and click Properties
2. In the Properties window, delete the address in the ScrollArea property box
3. Click on the worksheet, and you will be able to click and scroll freely (assuming that the sheet is not protected).

Set Scroll Areas With a Macro

The Scroll Area settings are cleared when you close the workbook, and you have to set them again, each time the file opens. You can use a macro to set them, as part of the Workbook_Open procedure.

Copy this macro to a regular module in your workbook. It will set the Scroll area for each worksheet in the file, based on the Used Range on that sheet.

Sub SetAllScrollAreas()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.ScrollArea = ws.UsedRange.Address
Next ws
End Sub

Add the macro name to the Worksheet_Open procedure in the ThisWorkbook module.

Private Sub Workbook_Open()
    SetAllScrollAreas
End Sub

Now, the ScrollArea settings will be added each time the file opens, if you enable macros.

Download the Sample File

To see the code and test worksheets, you can download my sample file. Go to the Worksheet Tips page on my Contextures site, and look for the Download Sample Files section.

Watch the Video

To see the steps for manually or programmatically setting the Scroll Area, watch this short video.

____________________