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.

Dataset-Excel Lock Cell Value 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.

Format Cells-Excel Lock Cell Value Once Calculated

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

Format Cells

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

Protect Sheet

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

Select Options

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.

Password Confirmation

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

Warning: Protected Sheet-Excel Lock Cell Value Once Calculated

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.

Enabling Protection option-Excel Lock Cell Value Once Calculated

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

Macro

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.

Format Cells

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

Warning: Protected Sheet-Excel Lock Cell Value Once Calculated

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


Similar Readings


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

Macro

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.

Warning

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

Unprotect

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.

Password Insertion

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

Formula Insertion

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.

Final outcome

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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo