Even if a spreadsheet is protected, you can scroll it in any direction and see what it contains. To prevent scrolling, you can change the ScrollArea property of the worksheet, manually or using a macro.
The Scroll Box setting is useful as a navigation aid and can discourage users from clicking or scrolling through the spreadsheet. However, it can be easily deleted, so don't rely on it to boost your file security.
NOTE: The scroll area must be set each time the workbook is opened. To automate this, use a macro, as shown below.
To manually set the scroll area:
On the ribbon, click the Developer tab, and then click Properties.
In the Properties window, enter an address range in the ScrollArea property box.
Click on the spreadsheet and you will be limited to clicking and scrolling in the scroll box.
To manually clear the scroll box:
On the ribbon, click the Developer tab, and then click Properties.
In the Properties window, delete the address in the ScrollArea property box
Click on the spreadsheet and you will be limited to clicking and scrolling in the scroll box.
The Scroll Area setting must be set each time the workbook is opened, and you can automate it with a macro. Copy the SetAllScrollAreas macro into a standard module in your workbook. It will set the scroll area for each worksheet in the file, based on the range used on that sheet.
Under SetAllScrollAreas()
Dim ws as spreadsheet
For each ws in ActiveWorkbook.Worksheets
ws.ScrollArea ws.UsedRange.Address
Next article
End of the submarine
Next, add the macro name SetAllScrollAreas to the Worksheet_Open procedure in the ThisWorkbook module. This will set the scrolling areas on all sheets, whenever the workbook is opened and macros are enabled.
private subclassifier_Open()
SetAllScrollAreas
End of the submarine
Please take the opportunity to connect and share this video with your friends and family if you find it useful.
No Comments