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

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 to lock cells in different ways with a unique dataset.


Download Practice Workbook

You can download the practice workbook from here.


2 Easy Ways to Lock Cells in Excel When Scrolling

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. Use Developer Tab to Lock Cells When Scrolling in Excel

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 the 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: Excel Not Scrolling with Arrow Keys (4 Suitable Solutions)


Unlock the Locked Cells

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 Unlock Cells in Excel When Scrolling (4 Easy Ways)


Similar Readings


2. Apply VBA Code to Lock Cells in Excel 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: How to Lock Rows in Excel When Scrolling (4 Easy Methods)


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

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo