It’s Macro Mondays time again. Today let’s look at limiting the range movement or the area a user is able to scroll around on an Excel worksheet. This is particularly useful for a number of reasons–
Prevents Clicking In Areas You Don’t Want Users To Click.
Sometimes, you want to limit the workspace that is visible to users so they do not wander into areas they have no reason to see and click around in. These areas could contain formulas or some data you do not want the user to see.
Visually You Can Control What The Worksheet Looks Like.
If you create an Excel dashboard, the final result usually looks a lot better visually if you can set the limits of the scroll area
Help Non Experiences Users Navigate Around the Worksheet Easier.
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 around and also get lost if they hit a key in error to take them further in columns or rows than the
So back to the Excel Macro. We can easily write a small piece of VBA code to pre set the area of the Excel worksheet to help resolve all of the above issues.
Download Macro Mondays Example Files.
What Does The Macro Do?
This macro will limit the area of a worksheet that you specify.
How Does It Work?
This Macro uses the ScrollArea property to set the scroll area for a worksheet that you choose, and by placing it in the Workbook_Open event code window allows it to run every time the workbook is opened. If we did not place the code in the Workbook_Open event code, it would have to be reset every time the workbook is opened.
So let’s get down the coding.
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic.
Step 1 .In the Project window find the name of the Project or workbook you want to place the code in.
Step 2. Click ThisWorkbook
Step 3. Select the Open event in the Event drop down 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 bit!.
Here is the code to copy and paste if you need to. Just replace your sheet name and scroll area as needed.
Private Sub Workbook_Open()
Sheets(“MM17”).ScrollArea = “B2:L17”
Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.