Wasim Akram
In some cases, you may need to protect your excel sheet but at the same time put data into the sheet. In Microsoft Excel, you can do this work easily. The following article is about how you can protect excel cells but allow data entry in a worksheet.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Simple Methods to Protect Excel Cells But Allow Data Entry
I have shared 2 simple methods to protect excel cells but allow data entry in excel.
Suppose we have a dataset of some patients’ Ages, Sex, and Blood Groups. Now we are going to protect cells from the excel dataset allowing value or text editing.
1. Protect Excel Cells But Allow Data Entry by Protecting Worksheet
In this following method, with the help of protecting a worksheet, you can protect excel cells at the same moment enter values or data.
Steps:
- Select cells to allow data entry.
- Press Ctrl+1 simultaneously to open the format cells window.
- In the “Format Cells” window go to “Protection”.
- Now uncheck the “Locked” option by clicking on it.
- Press OK to continue.
- Now the cells are no more locked.
- While selecting the cells go to “Review” from the home ribbon and from options click on “Protect Sheet”.
- A new window will open asking for a password to protect the sheet.
- Type a password of your choice and click OK to continue.
- After clicking OK another window will appear to confirm the given password.
- Type the same password again and press OK.
- Now check the result in the dataset. You will get your cells protected but at the same time, you will be enabled with entering data in those cells.
- If you try to edit data outside the selected range you won’t be able to do that. The following instruction will pop up.
Read More: Excel VBA to Protect Range of Cells (3 Examples)
Similar Readings
- How to Unlock Cells in Excel When Scrolling (4 Easy Ways)
- Excel VBA to Lock Cells without Protecting Sheet (4 Ideal Examples)
- How to Protect Cells without Protecting Sheet in Excel (3 Ways)
2. Run a VBA Code to Protect Excel Cells But Allow Data Entry
VBA (Visual Basic Applications) is the programming language of excel and other office programs. VBA is very effective and at the same time, it’s very efficient when it comes to repetitive solutions to formatting and correcting problems. In this method, I am describing how you can protect excel cells with allowing entering value or text by running a VBA code.
Steps:
- Select your sheet and press Alt+F11 to open the “Microsoft Visual Basic for Applications” window to run a VBA
- Select your sheet from the left column by double-clicking.
- Enter the code from the following into the module-
Sub AllowDataEntryOnly()
ActiveSheet.Protect Userinterfaceonly:=True, AllowFiltering:=True
Range("B4:F14").Locked = False
MsgBox "Only allow data entry in range B4:F14"
End Sub- Click Run.
- You will get a confirmation of your selected sheet which will be protected allowing data entry in the selected range.
- Now if you want to add data in cells other than the B4:F14 range, you will get a warning. That means, we have protected our cells but allowed data entry.
Read More: Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking
Things to Remember
- You might also need to unprotect the sheet after protecting the file with a password. To do that go to “Review” and select “Unprotect Sheet“. Now type the password and your sheet will be unprotected.
Conclusion
In this article, I have tried to cover the simple and quickest ways of protecting excel cells while allowing data entry. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.
