How to Set Specific Cells as Read Only/Locked in Excel

In our daily work, in case of protect our worksheet from being edit by others, we often add a password for opening worksheet. But sometimes, the worksheet is open for all or shared with your partners, we just want to set some important cells as read only in case of preventing others to edit them in worksheet, for example we need to set some cells with formulas or salaries as read only in payroll, how can we do? This article will help you to make your specific cells as read only.

Method 1: Lock Specific Cells in Worksheet by Format Cells Settings


Prepare a table with ID, Name, Score three columns. And we want to make some cells in this table cannot be editable by others.

A simple table for demonstration in this article:

Set Specific Cells as Read Only 1

Step 1: Press Ctrl+A to select the whole worksheet. You can also click on the arrow Set Specific Cells as Read Only 2 arrow button to select whole cells on current worksheet, see screenshot below.

Set Specific Cells as Read Only 3

Step 2: On worksheet anywhere, right click to load menu, select Format Cells. You can also load Format Cells window by press Ctrl+1.

Set Specific Cells as Read Only 4

Step 3: Format Cells window is loaded properly.

Set Specific Cells as Read Only 5

Step 4: Click on Protection, there are two options, Locked and Hidden, Locked option is checked by default. Uncheck Locked option, then click OK.

Set Specific Cells as Read Only 6

Notes:

1. You can see the comments under the option that locked or hidden options only take effective once protect the worksheet is enabled. So, we need to protect the worksheet then.

2. If we don’t uncheck Locked option first, and directly protect worksheet by password, then the whole worksheet will be locked. So this step is to make sure that only the selected range will be locked, please see the following step.

Step 5: Select the range you want to make them as Read Only, for example select A2:C4. Right click to load menu, select Format Cells again.

Set Specific Cells as Read Only 7

Step 6: This time, enter Protection tab, check on the Locked option, then click OK.

Set Specific Cells as Read Only 8

Step 7: Click Review->Protect Sheet.

Set Specific Cells as Read Only 9

Step 8: In Protect Sheet window, “Protect worksheet and contents of locked cells”, “Select locked cells” and “Select unlocked cells” are checked by default. Enter password in textbox and click OK, re-enter and confirm password again, then click OK.

Enter password:

Set Specific Cells as Read Only 10

Confirm password:

Set Specific Cells as Read Only 11

Step 9: Try to edit the table. Verify that below error message pops up.

Set Specific Cells as Read Only 12

Step 10: Click A3. Verify that user still can edit it due to it is not locked.

Set Specific Cells as Read Only 13

Step 11: If we want edit the locked cells, please click on Review->Unprotect Sheet. Unprotect Sheet pops up.

Set Specific Cells as Read Only 14

Step 12: Enter the password. Click OK. Now you can edit specific locked cells normally again.

Set Specific Cells as Read Only 15

Method 2: Lock Specific Cells in Worksheet by VBA


Step 1: Right click on Sheet1 to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic.

Step 2: In Microsoft Visual Basic for Applications window, click on Sheet1, enter below code:

Set Specific Cells as Read Only 16

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Then

If Target.Row = 2 Or Target.Row = 3 Or Target.Row = 4 Then

Beep

Cells(Target.Row, Target.Column).Offset(0, 1).Select

MsgBox Cells(Target.Row, Target.Column).Address & " You cannot edit the cell!", _

vbInformation, "Error Message"

End If

End If

End Sub

Comments:

1. In above script, Target.Column and Target.Row define the range will be locked. In this case, column 1,2,3 (A, B, C) and row 2,3,4 are included, that means range A2:C4 is selected and locked.

2. “You cannot edit the cell!” is the message pops up when user clicking on locked cell. “Error Message” is the header for pops up error message. You can replace them.

Step 3: Save the codes, see screenshot below. And then quit Microsoft Visual Basic for Applications.

Step 4: Try to edit the locked range A2:C4. Below error message pops up.

Set Specific Cells as Read Only 17

Step 5: Try to edit A1 which is not locked. Verify that user still can edit A1 properly. User can also edit other unlocked cells as well.

Set Specific Cells as Read Only 18

 

 

You might also like:

Sidebar