Rifat Hassan
In this article, I’ll show you how you can protect and hide formulas with Excel VBA on an Excel worksheet with a 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
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.
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).
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"
Read More: How to Protect Formula in Excel but Allow Input (2 Methods)
⧪ 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
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
Read More: How to Protect Formulas Without Protecting Worksheet in Excel
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.
⧪ Step 3: Putting the VBA Code
This is the most important step. Insert the given VBA code in the module.
⧪ 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.
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.
