Hafiz Islam
If you wish to protect your Excel worksheet but at the same time allow data entry into your Worksheet, MS Excel provides this facility to do this. In this article, you will learn 2 handy approaches to protect an Excel sheet but allow data entry.
Protect Excel Sheet but Allow Data Entry: 2 Handy Approaches
Let’s introduce our dataset first. We have data on some people’s first names, last names, states, and salaries. Our goal is to protect this Excel sheet but allow data entry.
1. Utilizing Protection Tab from Format Cells Option
To protect the Excel sheet but allow data entry, you have to unlock the cells first, then select the cells to be locked, and then you need to protect the worksheet. Please follow the steps below.
📌 Steps:
- First, press CTRL+A to select the entire worksheet.
- Now, press CTRL+1, and a Format Cells window will appear. Under Protect section, unmark the Locked checkbox, and finally, press OK.
- Again, select the cells you want to protect by password. Then, again press CTRL+1. A Format Cells window will appear.
- This time, check the Locked option and then click on OK.
- Then, go to the Review tab. Under Protect group, click on Protect Sheet. A Protect window will pop up.
- In the Password box, type your convenient password, and without doing anything more, press OK.
- A Confirm Password dialog box will appear. Reenter your password here. Then, press OK.
- Now, we can enter data into the cells except C4:C12 and D4:D12. For example, you can easily edit the cell F7.
- The cells C4:C12 and D4:D12 are password protected. Now, if you want to enter data in these cells, you will need to enter the password first.
Here is the gif for your better understanding of the result of this method.
Read More: How to Protect Excel Sheet with Password
Similar Readings
- How to Protect an Excel Sheet Except for Certain Cells
- How to Protect Excel Sheet from Deleting
- How to Protect Excel Sheet from Editing
2. Apply an Excel VBA Code
VBA (Visual Basic Applications) is a quite effective tool in case of repetitive solutions to various Excel problems. You can protect your Excel sheets but allow entering data in the cells by running a VBA code. Just follow the steps below to apply this amazing tool.
Steps:
- Select your worksheet and press the hotkey ALT+F11. It will open the “Microsoft Visual Basic for Applications” window.
- Or, manually click on Developer tab > Visual Basic > Insert > Module. A module dialog box will appear.
- Now, copy the following code and paste it into the module box. Finally, click on the Run button as shown below.
Sub ProtectSheetButAllowDataEntry()
ActiveSheet.Protect Userinterfaceonly:=True, AllowFiltering:=True
Range("B4:F12").Locked = False
MsgBox "Sheet is Protected but data entry is allowed"
End Sub
- Now your worksheet is protected. However, you can enter data in the cell range B4:F12.
Similar Reading
- Excel VBA: Protect Sheet with Password and Allow Filter
- Excel VBA to Protect Sheet but Allow to Select Locked Cells
Download Practice Workbook
Download the following Excel file for your practice. Password: 2022.
Conclusion
In this tutorial, I have discussed 2 handy approaches to protect Excel sheet but allow data entry. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.
