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.

Lock Scroll Area in Excel
Lock Scroll Area in Excel Image_01

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

Lock Scroll Area in Excel
Lock Scroll Area in Excel Image02

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.

Lock Scroll Area in Excel
Lock Scroll Area in Excel Image03

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.

Lock Scroll Area in Excel
Lock Scroll Area in Excel 04

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.

Lock Scroll Area in Excel
Lock Scroll Area in Excel

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.

First, write this VBA code and then save and close the workbook.

And then again open the workbook and see that all the worksheets scrolling area is locked now.

Here we have sheets where we can set or locked the scrolling area with the above VBA code in one go.

You can now add another sheet and set or lock the scrolling area for the sheet as well.

Just insert a new sheet and save and close the workbook.

And again open the workbook then see that your scrolling area for this sheet is also set.

So this is the permanent solution to set or locked the scrolling area in Excel.

I hope you find this tutorial useful.

Please feel free to put your comment or suggestion in the comment box below.

Related Post

How to Hide Blank Rows in Excel Using VBA Macro

How to Add Plus Sign (+) Before Positive Number in Excel

3 Quick Ways to Move or Copy a Worksheet into Another Workbook

3 Quick Ways to Change the Font Color in Excel VBA

How to Lock / Protect Formula Part Only In Excel (A Detailed Guide)

How to Split Cells Diagonally in Excel (Step by Step Guide)

Leave a Comment

Your email address will not be published.