Uncategorized – Page 3 – LearnYouAndMe

Lock or protect formula cells only

There are scenarios where you want to protect only formula cells of a sheet so that user can not accidentally delete or modify formulas.

Here I will explain step by step how to do this. Follow the below steps in series.

Make all cells unlock of a worksheet
Quickly select formula cells in Excel
Lock or protect formula cells
Hide underlying formula in Excel cells

4th step is optional, do it only if you do not want to show the cell’s underlying formula of a cell.

Note:- Before proceeding with steps make sure your worksheet is in unprotected state.

Make all cells unlock of a worksheet

First select all cell of a worksheet by click on left uppermost triangle in Excel sheet.

select all cells quickly in excel

Make sure selection is intact. Then right mouse click then click on Format Cells to bring Format Cells pane (dialog box). You can use CTRL+1 to directly bring Format cells pane.

In format cells pane go to Protection tab then untick Locked option then click on Ok button.

This will make all cells unlocked which means when you protect a worksheet these cells are still editable or you can write something in these cells.

format cells in excel

Quickly select formula cells in Excel

First select all cell of a worksheet by click on left uppermost triangle in Excel sheet as explained above.

Keeping selection intact press F5 function key to bring Go To pane. In Go To pane click on Special to bring Go To Special pane (dialog box).

In Go To Special pane tick on Formulas then click on Ok button to select only formula cells of a worksheet.

Quickly select formula cells in Excel

In the below image you can see only formula cells got selected. It is very important to keep this selection intact for next step.

Quickly select formula cells in Excel2

Lock or protect formula cells

Keeping formula cell selected press CTRL+1 to bring Format Cells pane.

In Protection tab tick on Locked option then click on OK button.

format cells making cell locked

Now you have to protect a worsheet. To protect worksheet go to Review tab click on Protect Sheet ,enter password then click on OK.

After doing steps 1 to 3 you are done with protecting only formula cells.

Using this method you can edit non formula cells but keeping formula cells locked.

protecting worksheet in excel

Now if you want to edit some formula cells then just unprotect worksheet do formula editions then again protect worksheet.

If you want to add new formula cells then unprotect worksheet, add formulas then do steps 1 to 3 in series again.

Hide underlying formula in Excel cells

If you do not want to show the underlying formula of a cell in the formula bar then you just have to tick both Locked and Hidden options while doing step 3 (Lock or protect formula cells).

After this you just protect worksheet by clicking on Review tab then select Protect sheet option.

hide cell formula in excel

VBA to lock formula cells of a worksheet

Option Explicit
Sub LockSheetFormula()
    'this protects formula cells of a specific sheet
    Dim pw As String, sht As Worksheet
    Dim formulaCells As Range
    pw = "123"
    'Sheet1 is code name of a worksheet
    Set sht = Sheet1
    sht.Unprotect pw
    On Error Resume Next
    Set formulaCells = sht.Cells.SpecialCells(xlCellTypeFormulas)
    If formulaCells Is Nothing Then Exit Sub
    sht.Cells.Locked = False
    formulaCells.Locked = True
    sht.Protect pw
End Sub

To use above Sub press ALT+F11 to open VBA IDE. Go to Insert tab then click on Module option to create one Standard Module.

Double click on newly created Module then copy paste above code in that module.

In module place cursor on any line then press F5 to run Sub or procedure for protecting only formula cells of a worksheet.

standard module in VBA

You can replace pw = "123" with pw = InputBox("Enter password") so that password in not stored in code and you have to input password in Input box.

You can change password “123” to some other string.

If you want to lock formula cells of active sheet then replace

Set sht = Sheet1 with Set sht = ActiveSheet

At place of Sheet1 which is code name of a worksheet you can write code name of other worksheet.

VBA to lock formula cells of a workbook (lock formula cells of all sheets)

Option Explicit
Sub LockSheetFormula()
    'this protects formula cells of all sheets
    Dim pw As String, sht As Worksheet
    Dim formulaCells As Range
    pw = "123"
    For Each sht In ThisWorkbook.Worksheets
        On Error Resume Next
        Set formulaCells = sht.Cells.SpecialCells(xlCellTypeFormulas)
        If formulaCells Is Nothing Then GoTo jmp
        sht.Unprotect pw
        sht.Cells.Locked = False
        formulaCells.Locked = True
        sht.Protect pw
jmp:
    Next sht
End Sub

To use above Sub press ALT+F11 to open VBA IDE. Go to Insert tab then click on Module option to create one Standard Module.

You can replace pw = "123" with pw = InputBox("Enter password") so that password in not stored in code and you have to input password in Input box.

You can change password “123” to some other string.

Place cursor on any line then press F5 to run Sub or procedure for protecting only formula cells of a worksheet.

VBA to lock formula cells fo all sheets