Excel Macro Tip: Limit Scroll Area or Range Movement in an Excel Worksheet

In this article, we will explore how to limit the movement of the range or 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 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 do not 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 bounds of the scroll area

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 get lost if they mistakenly press a key to take them further into columns or rows than the

So back to the Excel-Macro. We can easily write a small VBA code snippet to preset Excel worksheet area to help solve all the above problems.

How does the macro work?

This macro uses the ScrollArea property to set the scroll area of ​​a worksheet you choose, 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 lower the encoding.

FIRST. Open Visual Basic: Press ALT + F11 or the Developer: Visual Basic tab.

Step 1.In the Project window, find the name of the project or workbook in which you want to put 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 ​​worksheet MM17 to B2 to L17.

Step 4. Test your macro – the fun part!

Here is the code to copy and paste if needed. Just replace your sheet name and scroll area as needed.

private Subworkbook_Open()

Leaves (“MM17″). Scroll Area=”B2:L17”

finish sub