Protect Excel Cells But Allow Data Entry (2 Quick Methods)

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.

Protect Excel Cells But Allow Data Entry


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.

Protect Excel sheets by protecting worksheet

  • In the “Format Cells” window go to “Protection”.
  • Now uncheck the “Locked” option by clicking on it.
  • Press OK to continue.

 by Protecting Worksheet

  • 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”.

  • 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.

Protect Excel Cells But Allow Data Entry by Protecting Worksheet

  • If you try to edit data outside the selected range you won’t be able to do that. The following instruction will pop up.

Protect Excel Cells But Allow Data Entry by Protecting Worksheet

Read More: Excel VBA to Protect Range of Cells (3 Examples)


Similar Readings


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

Run a VBA Code to Protect Excel Cells But Allow Data Entry

  • 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.

Run a VBA Code to Protect Excel Cells But Allow Data Entry

  • 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.

Protect Excel Cells But Allow 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.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo