In this article, we will look at how to limit the movement of a range or area that a user can scroll through in an Excel sheet. This is especially useful for a number of reasons that we can explore below.
Prevents clicking in areas that you do not want users to click.
Sometimes you want to limit the workspace, which is visible to users, so that they do not wander through areas in which 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.
Visually, you can control what the worksheet looks like.
If you create an Excel toolbar, the end result usually looks much better visually if you can set the limits of the scroll pane.
Help users with no experience Move around the workspace easier.
Excel since 2007 has a maximum of 1,048,576 rows and 16,384 columns. This is a large area for the user to scroll through, and also a delusion if they click on a key error to take them further in columns or lines than
Back to Excel macro We can easily write a small piece of VBA code to pre-set the area of the Excel sheet to help solve all of the above problems.
How does the macro work?
This macro uses the ScrollArea property to set the scroll pane for a sheet of your choice and, by placing it in the event code window, Workbook_Open allows you to run it each time the workbook opens. If we have not placed the code in the code of the Workbook_Open event, it will need to be reset every time the workbook is opened.
So let's not encode.
FIRST. Open Visual Basic - by pressing 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 this book
Step 3. Select the event “Open” in the “Event” drop-down list and enter or copy the code below. This example limits the scrolling area of the MM17 sheet from B2 to L17.
Step 4. Check your Macro Fun! Bit.
Here is the code to copy and paste if you need. Simply replace the sheet name and scroll area as needed.
Private Sub Workbook_Open ()
Sheets ("MM17"). ScrollArea = "B2: L17"
End sub
