If you want to set a scroll area in a worksheet, for example A1:G50. User will not be able scroll beyond row 50 and column G.
To do this –
1. Right Click on a Sheet tab and click View Code.
OR
Press ALT+F11.
Now VBE Editor will open.
2. Set A1:G50 in Scroll Area to set Scroll Area to A1:G50.
3. If you want to reset this, you will have to make ScrollArea blanks.
CATCH – If you close the workbook and open it again, you will find, ScrollArea is blank i.e. this property gets reset. To handle this problem, you will need to do 3 lines of VBA coding and no need to follow steps 2 and 3 of above.
In VBA Editor which you opened in step 1, double click on This Workbook and copy and paste the below code –
Private Sub Workbook_Open()
Sheet1.ScrollArea = "A1:G50" 'Sheet1 can be replaced with your sheet name
End Sub
Save your file with .xlsm extension.
Close the Workbook and reopen again to make the code effective.
But a person knowing the above trick can follow step 1 and remove the code. To handle this problem –
In VBA Editor > Tool > VBA Project Properties > Protection > Check Lock Project for Viewing box and give passwords. Now, unless a person knows the password, he can not remove the code.