Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

Make cell as read only by protecting worksheet

Make cell as read only by VBA code without protecting worksheet


Protect multiple/all worksheets with password at the same time in current workbook::

Microsoft Excel provides 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 in current workbook.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Make cell as read only by protecting worksheet


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.


arrow blue right bubble Make cell as read only by VBA code without protecting worksheet

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.


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Praveen Kandrakota · 1 years ago
    Hi



    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.
      crystal · 1 years ago
      Good 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
      xSheet.Protect xStr
      xSheet.Activate
      End Sub
  • To post as a guest, your comment is unpublished.
    Ehtisham Amjad · 1 years ago
    i 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.
    Yahia · 1 years ago
    Hey, 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.
      crystal · 1 years ago
      Dear Yahia,
      The locked cells in a protected worksheet can't be copied.
  • To post as a guest, your comment is unpublished.
    rohit jain · 1 years ago
    Very Helpful. Thanks ! :D
  • To post as a guest, your comment is unpublished.
    mohammed mujeeb · 2 years ago
    Dear 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.