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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!