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:

x
How to Select Every Other Row in Excel

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

 

 

Related Posts

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

How To Insert Comments in Protected Worksheet in Excel
insert commetns in protected workshee3

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that ...

How To Convert Text to Upper Cases(Using VBA) in Excel
convert text to upper cases1

This post will show you how to switch from lower case to upper case in Excel. and I am going to show you two different ways of converting text to upper cases using formula or VBA macro in Excel 2013,Excel ...

How To Hide Every Other Row in Excel (Using VBA)
hide every other row1

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

How to Disable the Save As Prompt in Excel
disable save as prompt1

This post will show you how to use a VBA Macro to save an Excel file and overwrite any existing file without a prompt so that you are going to get the little window that says file already exists do ...

How to Count Cells that Contain even or odd numbers in Excel
count cells that contain even numbers5

This post will guide you how to count the number of cells that contain odd or even numbers within a range of cells using a formula in Excel 2013/2016.How do I count cells that contain odd numbers through the use ...

How to Count Cells that Contain negative Numbers in Excel

This post will guide you how to count the number of cells that contain negative numbers within a range of cells using a formula in Excel 2013/2016.You can count the number of negative numbers in your data using easy functions ...

How to Count Cells Are Not Blank or Empty in Excel
count non blank nonempty cells5

This post will guide you how to count cells that are not blank or empty in a given range cells using a formula in Excel 2013/2016.How do I count the number of cells that are not blank in a particular ...

How to Count Cells Less Than a Specific Value in Excel
count cells lessr than5

This post will guide you how to count the number of cells less than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are less than a specific ...

How to Count Cells Greater Than a Specific Value in Excel
count cells greater than 5

This post will guide you how to count the number of cells greater than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are greater than a specific ...

Sidebar