LOCK and PROTECT Cells Containing FORMULAS in an Excel sheet

LOCK and PROTECT Cells Containing FORMULAS in an Excel sheet

When you work on a spreadsheet, many a times you need to use some formulas. An Excel formula is easy to create and edit and I am sure all of you are well versed with it. You can easily edit it through the formula bar or directly in the cell.

LOCK and PROTECT Cells Containing Formulas

Editing formulas in the Formula Bar or directly in the Cell

But you may not want other users to change, edit or delete your formulas. At times, even you yourself may accidentally mess them up. Now if this happens, you might be lucky to trace and correct it. But if you’re not able to, you may end up wasting time and effort in recreating all of it. 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

https://www.telegraph.co.uk/finance/newsbysector/banksandfinance/11518242/Stupid-errors-in-spreadsheets-could-lead-to-Britains-next-corporate-disaster.html

One way to prevent this is to lock all the cells in the worksheet. But this way the user will not be able to make any changes at all. For instance, if you need inputs on the spreadsheet from someone or you have to get it reviewed by your manager, you may have to allow him to add comments or highlight cells as part of his feedback.

So a better way to deal with this problem is to lock only formulas in the spreadsheet. Now again if there are formulas in several cells and are scattered all over, it may be time consuming to look for cells containing formulas.

Video at the end

This article shows you a fast and easy way of protecting all your formulas in the worksheet at one go.

Below is 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()

‘ExcelJunction.com

Dim strPassword As String

With ActiveSheet

.Unprotect

.Cells.Locked = False

.Cells.SpecialCells(xlCellTypeFormulas).Locked = True

.Protect AllowDeletingRows:=True

strPassword = InputBox(“Please enter the password (optional)”, “Formula Protection powered by ExcelJunction.com”)

ActiveSheet.Protect Password:=strPassword

End With

End Sub

 

How to Use this Code?

Here are the steps to use this code:

  • (1) Open an Excel workbook. We’ll open one where we already have a lot of formulas.

Open an Excel file - Contains lots of formulas

 

  • (2) Go to Developer tab and click on Visual Basic or alternatively Press ALT + F11. This will open the Visual Basic Editor.

Open VBA Editor - Alt+F11

 

  • (3) Now in the VB Editor menu go to Insert and click on Module. This will insert a module for the workbook with a blank code window.

Insert Module in VBA Editor

 

  • (4) Now simply copy and paste our VBA code into this code window.

Copy and Paste the VBA Code

 

  • (5) Move back to the worksheet. To execute the code, go to developer tab and click on Macro. Alternatively Press Alt + F8 on your keyboard. The “Macro” dialog box will open. Click on “Run”

LOCK and PROTECT Cells Containing Formulas

 

  • (6) This will activate the code and Formula Protection Input box will prompt you for a password. Enter a password and make sure you remember it. You may even proceed without a password and still the code will protect your formulas. But this will be limited protection as anyone may easily get access to change formulas by unprotecting the worksheet without entering any password. However, if you have a password protection, then any changes to formulas would be allowed only once the password is entered correctly.

Execute MacroPassword Protection for Formulas

 

  • (7) Now that we have activated the code, lets try making changes to the cells containing formulas. So you see that changes cant be made now. Hence the code did its job well and the sheet is now protected from accidents.

Password required to make any changes to formulas in the worksheet

 

  • (8) If you want to make any changes willingly, simply “Unprotect” the worksheet from the Review tab. You shall be prompted for the password. Enter it and you’ll be able to make changes to all cells now. To re-protect, simply run the macro again and repeat the process.

Unprotect Sheet from Review tab to make changes to Cells containing Formulas

 

  • (9) Now save the Workbook and you are done. Just remember that it needs to be saved as an “Excel Macro-Enabled Workbook (*.xlsm)” for formula protection vba code to work.

Save the workbook - Macro Enabled

Excel Macro Enabled Workbook

 

Watch the Video