We protect Excel files or sheets so that other users or recipients cannot make any changes. But special cases arise sometimes. We may need to share our file with editing permission with no change of the formula cells. Because of the formula changes, we will not get the desired output. So, we are going to show how to protect the formula in an Excel sheet but allow input.
How to Protect Formula in Excel but Allow Input: 2 Methods
We will discuss two methods that describe how to protect formulas in Excel allowing input. One of them is a VBA macro.
We have a dataset of names of employees with their salaries and costs. Now, input the salary and cost and calculate the savings. We can not touch the savings column.
After inserting values in the Salary and Cost column, we get the savings automatically.
We also keep some empty cells. When new people come, we will insert their information and determine savings. Without the formula cells of the Savings column, other columns will remain editable.
1. Protect Formula Cells Only
We can protect the cells with formulas allowing data entry. First, lock the formula cells and then protect the sheet. Follow the below steps for details.
Steps:
- First, we will unlock all the cells. For that press Ctrl+A to select the whole worksheet.
- Then, go to the Format Cells window by pressing Ctrl+1.
- Uncheck the Locked option from the Protection tab. Finally, press the OK button.
- There is no locked cell on the worksheet now.
- Press the F5 button and enter the Go To window.
- Choose the Special button from that window.
- Select Formulas from the Go To Special window. Then, press OK.
- All cells containing formulas are marked here.
- Again, enter the Format Cells window.
- Now, check the Locked option and then press OK.
Cells containing formulas are locked now.
- Go to the Review tab.
- Click the Protect Sheet option from the Protect group.
- We will get a Protect Sheet. Here, will get the option for password protection.
- And also shows a list of allowed options for the user. We check the first two options, then press OK.
- Our work is now complete. We can input elements in any cell without the formula cells. Like, we input Allisa on Cell B9.
- But if we want to input in formula cells, we will get a warning. Here, we click on Cell E7 and the warning is showing.
Read More: How to Protect Formulas Without Protecting Worksheet in Excel
2. Use an Excel VBA Code to Protect Formula Cells and Allow Input in Other Cells
In this section, we will use a VBA code that will protect the formula cells allowing other cells editable.
Steps:
- Go to the Sheet Name section at the bottom of each sheet.
- Press the right button of the mouse. Choose View Code from the Context Menu.
- We enter the VBA window. Choose the Module option from the Insert tab.
- This is the VBA module. We will write VBA code here.
- Now, copy and paste the following VBA code on the module.
Option Explicit
Sub Protect_Formula_Cells()
Dim pass As String, w_sheet As Worksheet
Dim f_cells As Range
pass = "123"
Set w_sheet = ActiveSheet
w_sheet.Unprotect pass
On Error Resume Next
Set f_cells = w_sheet.Cells.SpecialCells(xlCellTypeFormulas)
If f_cells Is Nothing Then Exit Sub
w_sheet.Cells.Locked = False
f_cells.Locked = True
w_sheet.Protect pass
End Sub
- After that, press the F5 button to run the code.
We locked the formula cells successfully.
- We can input on any cells rather than the formula cells. Look, we can input Cell B10.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described 2 methods of how to protect formulas in Excel and allow input. We added a VBA code that works very fast the applying fewer steps. I hope this will satisfy your needs. If you have any questions or suggestions regarding the article, feel free to use the comment box.
Related Articles
<< Go Back to Excel Protect Formulas | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
