Several ways to limit the range of editing available for Excel worksheets

Because of our work needs, we often need to set access to a range of worksheets to prevent others from changing the contents of the cell range. Here are 3 ways to achieve this goal.

First, set the Scrollarea property

If you want to make the A1:e10 cell range of the worksheet unchanged (the same below), you can limit the scope of your use by hiding the range of a1:e10 cells by limiting the scope of the vertical scroll bar. The specific steps are as follows:

1. Perform the view → toolbar → Control toolbox command, and on the Open Control Toolbox toolbar, click the Properties button to display the Properties dialog box shown in Figure one.

Figure 1

2. In the Scrollarea column of the Properties dialog box, type "A45:e45" (The range can be selected), and then press ENTER.

After performing the above steps, we found that the A1:e10 cell range no longer appears in the worksheet window, and the vertical scroll bar cannot be moved. Because the Scrollarea property is not permanent, the file is closed and the next time you open it, you can optionally edit the area. It is therefore also necessary to add the following section of code to the ThisWorkbook code module.

　　Private Sub Workbook_Open()
　　Worksheets("sheet1").ScrollArea = "A45:E45"
　　End Sub

This way, each time you open the workbook, the code runs automatically and sets the Scrollarea property. This is a good choice as a way to protect worksheet content.

Ii. Use of worksheet protection

1. Activate the Sheet1 table, select the range of cells that allows the data to be populated, right-click to select Format cells, cancel the "lock" option on the Protection tab, and click OK to exit. Then click the menu "tools → protection → protect Sheet", in the pop-up dialog list, enter the protection password, check the "Select unlocked cells" option, all other options clear (as shown in Figure 2), click "OK" to return.

Figure 2

2, protect the worksheet, and then press ENTER or press the arrow keys, the cursor can only be unlocked from the original cell back and forth between the cells, and locked A1:e10 cell range cells can not be activated, so as to achieve the purpose of restricting access.

Third, the use of VBA to set access rights

We can also use the VBA code, set the password, when editing the SHEET1 worksheet a1:e10 cell range, automatically pop-up enter the password prompt box, the password is correct, the cell is activated, otherwise, the contents of the cell can not be changed. The specific methods are as follows:

To execute the tools → macros →visul basic Editor command or press the ALT+F11 key combination, in the Project Explorer window on the left side of the Code window, double-click the SHEET1 worksheet under the Excel object and enter the following code in the Code window on the right:

Private Sub Worksheet_Change(ByVal Target As Range)
X = Target
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <= 5 And Target.Row <= 10 Then
Y = InputBox("请输入密码：")
If Y <> 123 Then
MsgBox "密码错误，你无编辑权限!"
Range("A11").Select
End If
End If
End Sub