Often you may need to share your Excel file with others. And in that file, you may have many complex formulas or anything and you don’t want anyone to edit these things. For this, you may want to know how to make a cell in Excel read-only. So, none can edit those cells without a password. In this article, I will show you 2 easy methods so that you can learn how to make a cell in Excel read-only.
Download Practice Workbook
You can download the practice workbook from here:
2 Easy Methods to Make a Cell Read-Only in Excel
Here, assume that you have a datasheet of bank account owners. So, you want to make the ‘amount’ column cell ‘Read Only’. Here, I will show you 2 easy methods to make a cell read-only in Excel.
1. Make a Cell Read-Only by Using ‘Format Cells’ Command
By this method, you will lock the cells you want to make read-only then you will go to the “Protect Sheet” option to enable the protection. The steps are shown here:
- First, go to the file location and open your Excel file. Now select all cells of the worksheet by clicking on the top left corner or pressing Ctrl + A.
- Now, press the right button of the mouse to open options and select the “Format Cells” option. You can open “Format Cells” by pressing Ctrl + 1 keys.
There will open a dialog box.
- After opening the dialog box, press the “Protection” tab. And unmark the “Locked” button. Then press OK.
- Now, go to the Excel file again, and select the cells which you want to make read-only.
- Now, go to the “Format Cells” option again.
- And now, mark the “Locked” button under the “Protection” Tab. Then press OK.
- Now, go to the Review tab > Protect drop-down list > Protect Sheet button. And there will open a new window.
- Then, type the password (e.g. 1234) in the box first. You will see 3 checkboxes named “Protect worksheet and contents of locked cells”, “Select locked cells” and “Select unlocked cells” are already marked. Leave these boxes as they are. And press OK.
- Then, you will see another window that will ask you to re-enter the password for confirmation. So, re-enter the password there and press OK.
- After that, the selected cells will be locked. When you open the file again, you will see that you can’t edit the cells.
- To edit those cells, you have to unprotect them. To unprotect, go to Review > Unprotect Sheet. A window will appear and will ask you to enter the password. Now, enter the password here and press OK.
- By default, all the cells of the Excel sheet are in “Locked” in the format option. And the “Protect Sheet” option only protects the locked cells. We will see a message that we must mark under the password box.
- You may have a question about why you need to unmark the locked button at first. You have to unmark the locked button for all cells at first because without doing this, it will go to “Protected mode” for all cells. But you want it for specific cells.
- And, you have to do all the steps shown above to make the cells read-only. Doing only lock cells will not be effective without enabling the “Protect Sheet” option
Read More: How to Make Excel Workbook Read Only with Password (With Easy Steps)
2. Use a VBA Code to Protect Selected Cells
You can also write a VBA code to make a cell read-only by VBA code without using the “Protect Sheet” option. You will fix which cells to protect inside the code in this method.
- Right-click on the Sheet tab at the bottom. And select “View Code” from the menu.
- Then, there will open the Microsoft Visual Basic for Applications window. Just copy and paste the following code into the window.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 4 Then If Target.Row = 5 Or Target.Row = 6 Or Target.Row = 7 Or Target.Row = 8 Or Target.Row = 9 Then Beep Cells(Target.Row, Target.Column).Offset(0, 1).Select MsgBox Cells(Target.Row, Target.Column).Address & " Cell are read-only and protected ", _ vbInformation, "Cells Read Only" End If End If End Sub
- Now, press Alt + Q to close the window. And go to the Excel Sheet. You will see that the selected cells in the VBA code are in read-only mode. If you click on any of these cells, the following window will open. And you will be navigated to the adjacent cell.
- Now, save the file.
VBA Code Explanation:
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
→ This will be written by default when you open the Visual Basic window from the Sheet tab.
- If Target.Column = 4 Then
If Target.Row = 5 Or Target.Row = 6 Or Target.Row = 7 Or Target.Row = 8 Or Target.Row = 9 Then
→ You are selecting Column 4 and Row 5 to Row 9 which means you will select cells D5 to D9.
→ It will make a sound if you click on any of these cells.
- Cells(Target.Row, Target.Column).Offset(0, 1).Select
→ Statements to declare, you press on any of the selected cells.
- MsgBox Cells(Target.Row, Target.Column).Address & ” Cell is read-only and protected “, _
→ This statement declares, that if you press on any of the selected cells, a message box will appear and will say “Cell is read-only and protected”. The underscore is for the continuation of the macro.
- vbInformation, “Cells Read Only”
→ This will be the title of the message box.
- End If
→ The first End If will end the If function mentioning rows.
- End If
→ The second End If will end the If function mentioning column.
- End Sub
→ Finally, The last End If will end the sub-procedure of the macro.
- You have to open the Visual Basic window by right-clicking on the Sheet1 tab. So, it will open a macro selecting this worksheet and declaration to SelectionChange.
- You will not change the first line. It will same as by default. And after inputting the code just close the window. You don’t have to run it.
- This macro code is efficient for protecting a few selected cells. If you want to change which cells to protect, you have to make changes inside the code and fix the cells in the code.
- And lastly, to remove the read-only mode from the cells, just go to the visual basic window again and remove the code.
Read More: How to Make an Excel File Read Only
To conclude, this article tries to help learn how to make a cell in Excel read-only. Practice these methods on your own by downloading the practice workbook. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
- [Solved]: All Excel Files Opening as Read Only (6 Ways)
- [Fixed!] This Excel Workbook Was Opened in Read-Only Mode
- [Fixed!] Excel Files Opening as Read Only (13 Possible Solutions)
- [Fixed!] Excel Files Open as Read Only from Network (8 Quick Solutions)
- How to Remove Read Only from Excel (7 Easiest Ways)