How to Lock Scroll Area in Excel with VBA (Step By Step Guide)

This blog post shows you the step-by-step procedure to set or lock scroll area in Excel.

Suppose that you are working in a dataset and you do not want to allow the user to enter the data beyond that dataset.

In that case, you can set the limit for the user to not enter the data outside a locked area.

We can understand this with the help of the below-given image.

Here we do not want to permit or allow the user to enter the data outside of the selected dataset.

So the user cannot enter the data into the crossed area.

First, we must know which area we want to lock for scrolling in Excel.

In Excel, there are a couple of ways to set or lock the scrolling area with the help of VBA.

First of all, decide your scrolling area.

Here our scrolling area is range A1: E12.

We do not want to give access to user to enter the data beyond this range.

Then go to the ‘Developer’ tab

This step-by-step instruction will bring the Developer tab to your Excel Ribbon if you don’t see it.

Step 1. Right-click anywhere in the Ribbon area of Excel and select the ‘Customize the Ribbon’ option, from the listed options. See below image

Step2. After right-click, the ‘Excel Option’ box opens. See image below

Step 3. Go to the ‘Customize the Ribbon’ column, see the circled area ‘Developer’ here.

Step 4 – Just mark the check box as ‘tick’ and press ‘Ok’ at the bottom.

And all done.

The Developer tab is visible in your Excel Ribbon area now.

Now back to the blog post.

To set or lock scroll area in Excel, go to the ‘Developer’ tab in Excel Ribbon.

Click on click ‘Properties’ tab listed under this tab.

Once you click on the ‘Properties’ option, a property window opens, see the image below.

Find the ‘ScrollArea’ option in this window and enter your dataset range here in this place.

Once you entered the dataset range here, just close this window and see that your scrolling area is locked now.

And now you cannot enter the data outside of the dataset range.

This dataset has been locked, and the user will not be able to enter the data outside of this area now.

This is one of the easy methods to lock scroll area in Excel.

But when you close the file, you will lose all your saved settings.

Open the file again, you will lose your locked scrolling area.

So this is not the permanent solution to set the scrolling area.

Now we will find the permanent solution for it.

Lock Scroll Area in Excel Dynamically

You can find the permanent solution by returning to the Developer tab.

This time click on the ‘Visual Basic’ option from the ‘Ribbon’, see the image below

Once you click on this option, you will see the ‘Visual Basic for Application’ window.

See here in the ‘Project’ window, there is the ‘ThisWorkbook’ option is listed at the bottom.

Double-click on the ‘ThisWorkbook’ option here, and you will get the new VBA module, where you can write your VBA code.

Select the ‘Workbook’ option from the ‘General’ drop-down, as you can see in the screenshot below.

After selecting the ‘Workbook’ option from the list, you will get the below VBA code by default.

You can see the pre-written macro part is here.

And we can write our macro between these two lines for locking the scrolling part.

This VBA code will execute every time we will open this workbook.

Here we want to lock the area for Sheet (Week1).

Our VBA code will be as follows:-

Write this VBA code between these two lines and then save and close the workbook.

When you will open this workbook again, the VBA code will execute and your scrolling area will lock automatically.

This VBA code will execute every time whenever you open this workbook.

Here we have applied this VBA code for sheet ‘Week1’.

But what if we want to apply the same formatting or lock the same scroll area in our 2nd or 3rd sheet as well?

So in that case we will use the below code.

This is the code for if we want to set the scrolling area for the selected sheet only.

But if we have multiple sheets and we want to apply the same scrolling setting to all of our sheets.

For 2 or 3 sheets we can easily write our VBA code but what if we want to lock the same scrolling area for multiple or all our sheets then we will use the below VBA code dynamically.

This code will work for any number of sheets.