LOCK and PROTECT Cells Containing FORMULAS in an Excel Worksheet

You may not want other users to change, edit or delete your formulas. At times, even you yourself may accidentally mess them up. The probability of such errors increases a lot when you share a file with your colleagues, bosses or clients. Several corporates have lost millions due to spreadsheet disasters.

This video shows you a fast and easy way of protecting all your formulas in the worksheet at one go. There's a VBA code that’ll help us achieve this and it would not take more than a few seconds. Even those who are not acquainted with VBA can easily get this done by simply following the steps on how to use this code:

CODE

Sub ProtectFormulas()
Dim strPassword As String
With ActiveSheet
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect AllowDeletingRows:=True
ActiveSheet.Protect Password:=strPassword
End With
End Sub

[RInvestments]

Hi, great video, good and easy to follow explanation. Question: is there a way that I can not only protect the formulas but also hide them? So that other users can not see the formulas while clicking on the cells containing formulas? Thank you!

[raahman]

hi thanks for the video, i have question that protected sheet can be copied to other location and edited even though sheets are protected. please revert how to prevent such action

[aivy]

Will this work with Workbooks that have multiple sheets?