How to lock the screen to prevent scrolling in the Excel worksheet?

"Locking the screen to prevent scrolling in Excel" refers to a feature in Microsoft Excel that allows users to freeze certain rows or columns in a worksheet, keeping them visible while the rest of the sheet can be scrolled vertically or horizontally. This feature is useful when a user has a large dataset and wants to keep important headers or labels visible while working with the data. By locking the screen, the user can ensure that specific rows or columns remain in place while navigating through the rest of the spreadsheet.

In this example, three examples are used to perform the same task. The first example uses the properties setting available in the Excel worksheet, to perform the same task. The second example describes the process of using VBA code to lock the screen from preventing scrolling Excel. The third example is based on the use of Kutools.

Example 1: To Lock the screen to prevent scrolling in the Excel worksheet, by setting the available properties.

Step 1

Consider the following Excel spreadsheet. The below-given spreadsheet will have two columns of data. The first column contains data for the sample name, and the second column contains data for quantity. In this example will lock the worksheet screen to prevent scrolling by setting the properties manually.

Step 2

To lock the screen, open the Developer tab, and under the controls section, click on the “Properties” option. For proper reference, consider the below-given image −

Step 3

The above step will open a “Properties” tab. The properties dialog box contains many options for different available properties. Among the available list of properties. The most required property is the ScrollArea.

Step 4

In the opened scroll area, for this example will take the cell area from A1 cell to K15 cell. here, it is necessary to understand that the area, will vary from one user to another. But, here, will assume this due to our window snapshot area. However, user can set any required location, according to convenience.

Step 5

After writing the provided cell range, try to move the spreadsheet, beyond the 15th row, and Column K. user will not be able to move the scroll pane due to the setting restrictions.

Example 2: To Lock the screen to prevent scrolling in the Excel worksheet, by using the VBA code.

Step 1

To understand the process of locking the screen to prevent scrolling in Excel by using the VBA code. let’s consider the same Excel spreadsheet.

Step 2

After that go to the sheet 1 tab and use right click. This will display an available list of options. From the available list of options, choose the option “View Code”. Consider the below-depicted image for reference −

Step 3

The above step will open a dialog box for “Microsoft Visual Basics for Applications”. This dialog box, contains an empty code area, as depicted below. In the opened area, paste the below-specified code −

' define open_wrkbook method
Private Sub open_wrkbook()
   ' setting required scrollarea
   Sheets("Sheet1").ScrollArea = "A1:K15"
   ' end of module
End Sub 

Code snapshot for proper reference

Ensure, the use of proper code indentation to avoid errors

Step 4

Click on the “Run” option to run the available code. Consider the below outlined or highlighted button option.

Step 5

After running the provided cell range, try to move the spreadsheet, beyond the 15th row, and Column K. user will not able to move the scroll pane due to the setting restrictions passed inside the VBA code.

Example 3: To Lock the screen to prevent scrolling in Excel worksheet, by using the kutools.

Step 1

Consider the below given excel spreadsheet. Select the area user wants to lock. For this example, assume that the user wants to lock the data from the A1 cell to the K15 cell.

Step 2

Open the “Kutools” tab, and then under the view section click on the “Show/Hide” option, and further select the option “Set Scroll Area”.

Step 3

After running the provided cell range, try to move the spreadsheet, beyond the 15th row, and Column K. The user will not be able to move the scroll pane due to the setting restrictions passed inside the VBA code.

Conclusion

This article allows the user to lock the screen by preventing the scroll pane in Excel. This article contains three examples, the first example allows the user to prevent scrolling by setting the available properties, the second examples enforce the use of VBA code, and the last example, allows the user to use Kutool to perform the specified task. The guided article is detailed and thorough.