How to Lock Cell Value Once Calculated in Excel (3 Simple Ways)

In Excel, users execute sensitive calculations which need to be unaltered once calculated. As a result, the Excel lock cell value once calculated is a handy option to maintain the authenticity of a document. Protect Sheet and VBA Macro code can lock the cell values once calculated.

Let’s say we have Employee Working Time data from which we want to calculate the Working Hours. Also, we want no further alteration of these calculated values once they are calculated.

Download Excel Workbook

3 Easy Ways to Lock Cell Value Once Calculated in Excel

There is a different outcome between Lock Cells and Protect Sheet executions. Lock Cells; Font Setting > Format Cells > Protection > Locked, lock selected cells. However, users are still able to modify or change the calculated values within the selection. To ensure cell locking once the value is calculated, users have to execute Protect Sheet feature after enabling the Cell Protection.

Method 1: Lock Value Cells Using Format Cells Feature in Excel

Though locking cells don’t really save cells from getting altered, we have to enable it before applying the Protect Sheet feature.

Step 1: Select desired cells within a Worksheet after the calculation. Then click on the Font Dialog Box Icon to bring out the Font Setting dialog box. Alternatively, you can use CTRL+1 to do so.

Step 2: The Format Cells window appears. In the window, Choose Protection > Tick the Locked option. Click OK.

What is done here is that Excel is asked to lock those cells but it can’t do it without the Sheet Protection ON. So, you have to protect the sheet to ensure no further alteration of value.

Step 3: Now, go to the Review tab > Click on Protect Sheet (from Protect section).

Step 4: Excel fetches the Protect Sheet dialog box. Provide a suitable Password (i.e., 1234) then click OK.

You can choose from multiple options what you want to allow other users to do with your worksheet without a password.

Step 5: A confirmation window pops up. Enter the same password to proceed.

🔼 Afterward, in the Worksheet try to edit or alter calculated values. You see Excel displays a warning saying the Sheet is a Protected Sheet and you may need the password to unprotect it as depicted in the following image.

So, none can alter any calculated values without a password after you execute the sheet protection.

Read More: How to Protect Excel Cells with Formulas (2 Simple Methods)

Method 2: Using VBA Macro to Enable Protection Option

Alternative to the Method 1, the cell Protection option (i.e., Locked) can be enabled by a Macro code execution. Running this macro will lock the cells provided in the Macro.

Step 1: Use ALT+F11 or go to the Developer tab > Visual Basic (in the Code section) to display the Microsoft Visual Basic window. Double Click on any Sheet (i.e., Sheet2). It displays a Sheet’s Code window to insert macro codes.

Step 2: Paste the below Macro code in the Sheet’s Code window.

Private Sub Worksheet_Change(ByVal Rng As Range)
If Range("C4") = "Undone" Then
Range("E6:E16").Locked = False
ElseIf Range("C4") = "Done" Then
Range("E6:E16").Locked = True
End If
End Sub

The macro runs a VBA IF formula to execute a statement that holds cells (i.e., E6:E16) to enable the Protection Lock.

Step 3: After the Macro insertion, back to the worksheet and find whether the macro locks the cell or not. Go to Font Setting Icon > Format Cells > Protection or CTRL+1 > Protection. You see the macro enables the Locked option in the Protection section.

🔼 Repeat Steps 3 to 5 of Method 1 to ensure locking of the cell values after calculation.

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

Similar Readings

• How to Protect Excel Cells from Deletion (3 Handy Methods)
• Protect Excel Cells But Allow Data Entry (2 Quick Methods)
• How to Protect Certain Cells in Excel (2 Suitable Ways)

Method 3: VBA Macro to Lock Cell Value Once Calculated in Excel

We can also write a couple of line macro codes to lock cell values once calculated. This requires entering a password every time users want to perform any change or modification to the sheet.

Step 1: Execute Steps 1 and 2 of Method 2 then Paste the below macro in the Sheet’s Code window.

Option Explicit
Sub Worksheet_Change(ByVal Rng As Range)
ActiveSheet.Unprotect Password:="1234"
Rng.Locked = True
ActiveSheet.Protect Password:="1234"
End Sub

The macro performs ActiveSheet.Protect/Unprotect statement using the password (i.e., 1234) every time you perform a calculation or edit.

Step 2: Now, get back to the worksheet and try to calculate the Time Worked in range E8:E17. Excel displays a warning that the sheet is a protected sheet, you need a password to be able to edit.

Step 3: Go to Review > Unprotect Sheet (in the Protect option).

Step 4: The Unprotect Sheet command box appears. Provide the Password (i.e., 1234) to unlock the sheet for a single execution of any command. Afterward, Excel locks the entire sheet again.

Step 5: Execute only one command (i.e., Subtraction). Then Hit ENTER.

Step 6: As you know from the previous arguments that the Sheet gets locked after each execution of the command. You need to Unprotect the Sheet using Steps 3 and 4 to be able to perform further operations each time. The final outcome will look similar to the picture shown below.

Read More: Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking

Conclusion

Protect Sheet and VBA Macro Code lock cell value once calculated in Excel. Hope these described methods fulfill your requirements. Comment if you have further inquiries or have anything to add.

Related articles

• How to Protect Excel Cells from Being Edited (6 Suitable Methods)
• Unlock Cells in Excel When Scrolling (4 Easy Ways)
• How to Protect Cells without Protecting Sheet in Excel (3 Ways)