How to make cell as read only in Excel?
In many cases, you may need to prevent others from editing some specified cells in your worksheet. This article provides two methods to help you make specified cells as read only, so that others cannot edit these cells except for reading.
You can only lock the cells which you want to make them read-only, and then protect this worksheet to achieve it. Please do as follows.
1. Click the button on the top left corner of current worksheet to select the whole cells.
2. Press the Ctrl + 1 keys simultaneously to open the Format Cells dialog box. In the dialog box, uncheck the Locked box under the Protection tab, and then click the OK button. See screenshot:
3. Now select the cells you want to make them as read only (here I select cells of range A1:C18). Press the Ctrl + 1 keys simultaneously to open the Format Cells dialog box, check the Locked box under the Protection tab and click the OK button.
4. Go ahead to click Review > Protect Sheet. See screenshot:
5. Specify and confirm your password to protect the worksheet as below screenshot shown.
Now cells in the locked range are read-only now. When you try to edit these specific cells inside the range, you will get a prompt box as below screenshot shown.
Protect multiple/all worksheets with password at the same time in current workbook:
Microsoft Excel allows you to protect one worksheet with password at a time. Here you can use the Protect Worksheet and Unprotect Worksheet utilities of Kutools for Excel to quickly protect or unprotect multiple worksheets or all workrksheets in current workbook with password at the same time.
Download and try it now! ( 30-day free trail)
If you don’t want to protect the worksheet to make cell as read only. The VBA script in this section will help you to solve this problem easily.
1. Right click the sheet tab you need to make cell as read only, and then click View Code from the right-clicking menu.
2. In the opening Microsoft Visual Basic for Applications window, copy and paste the below VBA code into the Code window.
VBA code: Make cell as read only in Excel
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 Then If Target.Row = 3 Or Target.Row = 4 Or Target.Row = 5 Then Beep Cells(Target.Row, Target.Column).Offset(0, 1).Select MsgBox Cells(Target.Row, Target.Column).Address & " cannot be selected and edited as it is a read-only cell", _ vbInformation, "Kutools for Excel" End If End If End Sub
Note: This code can just make specified cells in one column as read only. In this code, it will make cell A3, A4 and A5 as read only in current worksheet.
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
When clicking on cell A3, A4 or A5 in current worksheet, you will get a Kutools for Excel dialog box as below screenshot shown, and the cursor will move to the right adjacent cell automatically.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 months agoThanks for the quick guidance.
- To post as a guest, your comment is unpublished.· 2 years agoHi
I have mulipe sheets Menu ( option A1& A2) , A1 and A2 and VIEW work sheets ( where i will have BROWSE button) .
1. In menu i will select A1..
2. it will go to VIEW sheet ( which is working fine) till now ..
3. when i click on the BROWSE in the VIEW sheet A1 shee shoulf get displayed and the content present in the cells should be non editable..
Could you please help..
- To post as a guest, your comment is unpublished.· 2 years agoGood day,
Supposing there is a Command Button (ActiveX Control) in the View sheet. After inserting the below VBA into the worksheet code window, click on the button, in a popping up Kutools for Excel dialog box, specify a password to protect the sheet A1. Then sheet A1 is opened and non editable.
Private Sub CommandButton1_Click()
Dim xSheet As Worksheet
Dim xStr As String
On Error Resume Next
Set xSheet = Sheets("A1")
If xSheet Is Nothing Then Exit Sub
xSheet.UsedRange.Locked = True
xStr = Application.InputBox("Please specify a password to protect the sheet A1", "KuTools for Excel", , , , , , 2)
If xStr = False Or xStr = "" Then Exit Sub
- To post as a guest, your comment is unpublished.· 2 years agoi just want to make few cells of a book locked for input, only they shows the result as i programmed them to do so
- To post as a guest, your comment is unpublished.· 2 years agoHey, Thanks a lot for this steps.
However, what if I want to copy the values in the cells that I have locked?
- To post as a guest, your comment is unpublished.· 2 years agoDear Yahia,
The locked cells in a protected worksheet can't be copied.
- To post as a guest, your comment is unpublished.· 3 years agoVery Helpful. Thanks ! :D
- To post as a guest, your comment is unpublished.· 3 years agoDear i want to protect the hidden cells from by copying to the new sheet, is there any option in the excel sheet for that. Means no can able to view the formulaes after copying from one sheet to other sheet.