In this article, we will explore how to limit the movement of the range or the area that a user can scroll in an Excel spreadsheet. This is particularly useful for a number of reasons that we can explore below.

Avoid clicking areas that you don’t want users to click.

Sometimes you want to limit the workspace that is visible to users so they don’t wander through areas that they have no reason to see and click on. 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 generally looks much better visually if you can set the scroll area limits

Help inexperienced users navigate the worksheet more easily.

Excel 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 down columns or rows than the

So let’s go back to Excel macro. We can easily write a small VBA code snippet to preset the Excel worksheet area to help solve all the above problems.

How does the macro work?

This macro uses the ScrollArea property to set the scrolling area for a worksheet of your choice, and placing it in the Workbook_Open event code window allows it to run every time the workbook is opened. If we don’t put the code in the Workbook_Open event code, it should restart every time the workbook is opened.

So let’s take a look at 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 place the code.

Step 2. Click on ThisWorkbook

Step 3. Select the Open event from the Event drop-down list and type or copy the code below; This example limits the scrolling area of ​​worksheet MM17 to B2 to L17.

Stage 4. Put your macro to the test – the fun!

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

Private Child Book_Open ()

Sheets (“MM17”). ScrollArea = “B2: L17”

End Sub