How to Lock Cells in Excel When Scrolling (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, we are going to show you how to lock cells in Excel when scrolling. After this article, you will be able to lock a specific range of cells from your worksheet to restrict your users so that they can only select and scroll cells within a specific range of a worksheet. Also, they will not be able to go outside the specified range and make any changes to that worksheet. Throughout this article, we will explain the process of locking cells in different ways with a unique dataset.


In this article, we will explain 2 ways to lock screens in Excel when scrolling. To make you understand better we will use the same dataset for explaining both methods. We have given a screenshot of our dataset in this section. The range of the dataset is (B4:E15) and it contains the data of Salesperson their Location, Region and “Total Amount” of sales. Our target is to restrict a user only within the range (B4:E15) by locking the cells. So, the user will only be able to select and scroll within this cell range.

2 Easy Ways to Lock Cells in Excel When Scrolling


1. Using Developer Tab to Lock Cells in Excel When Scrolling

In the first method, we will use the Developer tab to lock cells. This method will restrict users from modifying any kind of data outside the cell range (B4:E15).

Use Developer Tab to Lock Cells When Scrolling in Excel

Now, let’s see the steps to how we can perform this action.

STEPS:

  • Firstly, go to the Developer tab.
  • Secondly, select the option Properties from the ribbon.

Use Developer Tab to Lock Cells When Scrolling in Excel

  • The above actions open a new dialogue box named Properties.
  • Next, go to the option ScrollArea from that box.

Use Developer Tab to Lock Cells When Scrolling in Excel

  • Thirdly, insert the cell range (B4:E15) manually in the input box of the option ScrollArea.

Use Developer Tab to Lock Cells When Scrolling in Excel

  • The above command locks the cells within the range (B4:E15).
  • Then, select cell B4 and scroll rightwards with the keyboard.

Use Developer Tab to Lock Cells When Scrolling in Excel

  • So, the users will only be able to scroll to cell E5 as they do not have access to scroll outside the range (B4:E15).

  • Similarly, select cell E4 and scroll downwards with the keyboard.

  • Lastly, we can see that we also can not scroll after cell E15 which is the last value of our cell range.

Read More: How to Unlock Cells in Excel When Scrolling


Unlock the Locked Cells in Excel

Now, if we want to unlock the cell range we can do it easily. We will do this with the following steps.

STEPS:

  • In the beginning, select the Properties option from the Developer tab again.

  • The command opens the dialogue box named Properties.
  • Next, go to the option ScrollArea.
  • Then, delete the previous range from the input box of the ScrollArea and keep it blank.
  • After that, press Enter.

Unlock the locked Cells

  • Finally, the above action unlocks the cell range again.

NOTE:

This method works until you keep your workbook or worksheet active. If you close your worksheet or workbook and reopen it you will see that the lock cells feature doesn’t work anymore. So, to lock cells permanently we will use the second method of this article.

Read More: How to Lock Rows in Excel When Scrolling 


2. Applying Excel VBA Code to Lock Cells When Scrolling

To lock cells permanently in Excel when scrolling we will use a simple VBA (Visual Basic for Applications) code in our worksheet. This code will lock specific cell ranges from our worksheet. If you lock cells with VBA code then you will not lose the lock feature after closing the Excel file. As we have said before to illustrate this method we will continue with the same dataset that we used before.

Apply VBA Code to Lock Cells in Excel When Scrolling

Now, just go through the steps given below to lock cells in Excel when scrolling.

STEPS:

  • First, right-click on the sheet “Use VBA Code”.
  • Next, select the option “View Code” from the available options.

Apply VBA Code to Lock Cells in Excel When Scrolling

  • Now, a blank VBA module will open.
  • Then, Insert the following code in that blank module:
Sub Lock_Scroll()
Worksheets("Use Vba Code").ScrollArea = "B4:E15"
End Sub
  • Click on Run or press F5 to run the code.

Apply VBA Code to Lock Cells in Excel When Scrolling

  • The above command will lock the cell range (B4:E15).
  • Then, select cell B5 and scroll rightward with the keyboard.

Apply VBA Code to Lock Cells in Excel When Scrolling

  • We can see that we can not scroll after cell E4 as the next cell F4 is outside the given range.

  • Similarly, select cell E4. Then just scroll downwards with the keyboard.

  • Finally, we can see that we can not scroll after cell E5 as the locked cell range is (B4:E15).

Read More: Unfreeze Rows in Excel


Download Practice Workbook


Conclusion

In conclusion, this tutorial will guide you on how to lock cells in Excel when scrolling. To put your skills to the test, use the practice worksheet that comes with this article. Please leave a comment below if you have any questions. Our team will try to reply to you as soon as possible. In the future, keep an eye out for more unique Microsoft Excel solutions.


Related Articles


<< Go Back to Freeze Panes | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo