In this article, we’ll explore how to limit the range of movement or the area that a user can scroll through in an Excel spreadsheet. This is particularly useful for a number of reasons that we can explore below.
Avoid clicking on areas that you don’t want users to click on.
Sometimes you want to limit the workspace that is visible to users so they don’t wander into areas they have no reason to see and click. These areas may contain formulas or some data that you don’t want the user to see.
You can visually control the appearance of the worksheet.
If you create an Excel dashboard, the end result usually looks much better visually if you can set the scroll area boundaries
Help inexperienced users navigate the worksheet more easily.
Excel from 2007 onwards has a maximum number of 1,048,576 rows and 16,384 columns. This is a large area for a user to scroll through and also miss if they mistakenly press a key to take them further down columns or rows than the
So back to the Excel-Macro. We can easily write a small VBA code snippet to preset the Excel worksheet area to help solve all the above issues.
How does the macro work?
This macro uses the ScrollArea property to set the scroll area of a worksheet of your choosing, and by placing it in the Workbook_Open event code window, allows it to run each time the workbook is opened. If we didn’t put the code in the Workbook_Open event code, it would have to be reset every time the workbook is opened.
So let’s go down the coding.
FIRST. Open Visual Basic: Press ALT + F11 or the Developer tab: Visual Basic.
Step 1.In the Project window, find the name of the project or workbook in which you want to place the code.
Step 2. Click on this workbook
Step 3. Select the Open event from the Event dropdown list and type or copy the code below: This example limits the scroll area of the MM17 worksheet to B2 at L17.
Stage 4. Test Your Macro – The Fun Part!
Here is the code to copy and paste if necessary. Just replace your sheet name and scroll area as needed.
private subworkbook_Open()
Sheets (“MM17″). Scroll Area=”B2:L17”
finish sub