In this write-up we will explore how to restrict the selection movement or the region a user is in a position to scroll close to on an Excel worksheet. This is specially practical for a quantity of motives which we can take a look at beneath.
Prevents Clicking In Areas You Do not Want People To Click.
In some cases, you want to limit the workspace that is noticeable to buyers so they do not wander into areas they have no rationale to see and click on about in. These places could consist of formulation or some facts you do not want the person to see.
Visually You Can Management What The Worksheet Appears to be like Like.
If you generate an Excel dashboard, the closing result ordinarily looks a ton far better visually if you can established the limits of the scroll spot
Support Non Activities Customers Navigate About the Worksheet Much easier.
Excel from 2007 onwards has a utmost variety of 1,048,576 rows and 16,384 columns. This is a large area for a user to scroll all over and also get missing if they strike a crucial in mistake to just take them even more in columns or rows than the
So again to the Excel Macro. We can simply publish a modest piece of VBA code to pre established the space of the Excel worksheet to assistance resolve all of the over concerns.
How Does The Macro Work?
This Macro works by using the ScrollArea assets to set the scroll place for a worksheet that you decide on, and by placing it in the Workbook_Open celebration code window permits it to operate each individual time the do the job guide is opened. If we did not location the code in the Workbook_Open up party code, it would have to be reset every single time the do the job e-book is opened.
So let’s get down the coding.
Very first. Open Visible Standard – by hitting ALT +F11 or Developer Tab – Visible Fundamental.
Step 1.In the Project window come across the identify of the Challenge or work ebook you want to position the code in.
Move 2. Simply click ThisWorkbook
Move 3. Select the Open celebration in the Function fall down record and style or copy the code beneath – this example limitations the scroll location of worksheet MM17 to B2 to L17.
Move 4. Take a look at Your Macro – The Fun little bit!.
Here is the code to duplicate and paste if you need to have to. Just exchange your sheet name and scroll place as wanted.
Non-public Sub Workbook_Open()
Sheets(“MM17”).ScrollArea = “B2:L17”
Finish Sub
