Excel VBA: Protect and Hide Formulas

In this article, I’ll show you how you can protect and hide formulas with Excel VBA on an Excel worksheet with password.


Excel VBA: Protect and Hide Formulas (Quick View)

Sub Protect_and_Hide_Formulas()

Sheet_Name = "Sheet1"
Cell_Range = "D4:D13"
Password = "ExcelDemy"

With Worksheets(Sheet_Name)
    .Range(Cell_Range).Locked = True
    .Range(Cell_Range).FormulaHidden = True
    .Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=True
End With

End Sub

VBA Code to Protect and Hide Formulas in Excel VBA


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


An Overview of the VBA Code to Protect and Hide Formulas in Excel (Step-by-Step Analysis)

So, without further delay, let’s go to our main discussion today. Here we have got a worksheet called Sheet1 that contains the names of some employees of a company, their starting salaries, and the present salaries.

Data Set to Protect and Hide Formulas using Excel VBA

The present salaries of the employees depend on their starting salaries, but the formula for each employee is different from that of the other employees (It depends on the performance of the employees).

Formulas to Protect and Hide Formulas using Excel VBA

Our objective today is to hide and protect the formulas of the present salaries using VBA.

We’ll break down the VBA code step-by-step to learn how to protect and hide the formulas in an Excel worksheet.

⧪ Step 1: Inserting the Necessary Inputs

At the very outset of the code, we have to insert the necessary inputs required in this code. They are:

  • The Worksheet Name: Here it’s Sheet1.
  • The Range of Cells with the Formulas: Here it’s the range D4:D13.
  • The Password: Let it be ExcelDemy.
Sheet_Name = "Sheet1"
Cell_Range = "D4:D13"
Password = "ExcelDemy"

⧪ Step 2: Protecting and Hiding the Formulas

Next is the most important step. We’ll protect and hide the formulas of the worksheet using the password.

With Worksheets(Sheet_Name)
    .Range(Cell_Range).Locked = True
    .Range(Cell_Range).FormulaHidden = True
    .Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=True
End With

VBA Code Section to Protect and Hide Formulas using Excel VBA

So the complete VBA code will be:

VBA Code:

Sub Protect_and_Hide_Formulas()

Sheet_Name = "Sheet1"
Cell_Range = "D4:D13"
Password = "ExcelDemy"

With Worksheets(Sheet_Name)
    .Range(Cell_Range).Locked = True
    .Range(Cell_Range).FormulaHidden = True
    .Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=True
End With

End Sub


Developing a Macro to Protect and Hide Formulas Using Excel VBA

We’ve seen the step-by-step analysis of the code to protect and hide formulas using a password in Excel VBA.

Now, we’ll see how we can develop a Macro using the code.

⧪ Step 1: Opening the VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.

Inserting New Module to Protect and Hide Formulas using Excel VBA

⧪ Step 3: Putting the VBA Code

This is the most important step. Insert the given VBA code in the module.

Putting VBA Code to Protect and Hide Formulas using Excel VBA

⧪ Step 4: Running the Code

Click on the Run Sub / UserForm tool from the toolbar above.

The code will run. Once the code is run, the formulas of the given range will be hidden.

Hidden Formulas to Protect and Hide Formulas using Excel VBA

Also, the worksheet will be protected with a password. You can’t edit or make any changes to it unless you unprotect it with the password.


Things to Remember

The moment you run the code, the worksheet will be protected and the formulas will be hidden. You can’t edit it anymore or see any of the formulas unless you know the password and unprotect the sheet with it. Therefore, it’s recommended not to run the code before you understand every bit of the code and have a clear idea of how to recover a protected sheet in Excel.


Conclusion

Therefore, this is the process to develop a Macro to protect and hide formulas with a password in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo