It’s Macro Mondays time again. Have you ever been working on an Excel spreadsheet and had the scroll bar just go crazy, making it hard to navigate to the cells you need? Well, there is a way to limit the range of movement for the scroll bar. In this blog post, we will show you how to do that. So, whether you are a beginner or an Excel expert, read on to learn more. Specifically in I will demonstrate how to write a simple Excel VBA macro to limit the range movement or the area a user is able to scroll around on an Excel worksheet. Why would you want to enable an Excel scroll lock?. There are a number of reasons. Let’s talk through them.
Reasons to Use an Excel Scroll Lock.
1.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.
2. 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
3. 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 scroll lock 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.
What Does The Macro Do?
This macro will limit the area of a worksheet that you specify. It is really simple and effective.
How Does It Work?
This Macro uses the ScrollArea property to set the scroll area of Excel scroll lock for a worksheet that you choose. The code is placed it in the Workbook_Open event code window. This 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 to the coding and get started.
Step 1. Opening The Visual Basic Window.
As always, the first step is to open the visual basic winow. If you do not see this option available i your Excel menu tabs then it will require enabling. This is simple. You can read my blog post and instruction in the link here. Once you have the Visusal Basic Window, find you project and or name of the workbook to place your code in.
Step 2. Using The Workbok_Open Event.
Next, click on This Workbook within the project name or the workbook to use the Excel scroll lock code. Select the Open event from the right hand drop down menu.
Step 3. The Excel Scroll Lock Vb Code
So, now it is time to insert the code into this Workbook event. This example limits the scroll area of worksheet MM17 to B2 to L17. Chnage this part of the code to suit your own Excel scroll lock requirements.
Step 4. Test The Macro.
Finally, it is time to text the macro. My all time favourite part, of course.
Excel scroll lock is a great way to limit the data that your users can see and edit on a worksheet. You can create an Excel scroll lock by using a macro. This will help you protect sensitive data and ensure that your users are seeing only the data that you want them to see. If you want to learn more about how to create an Excel scroll lock, be sure to sign up for our newsletter. We’ll send you regular updates with information about all things Excel, including how to use macros like this one!
Do You Want To Cop The Code?
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”
End Sub