VBA Protect Sheet

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Protecting Sheet

We can protect the Excel sheet using VBA code which does not allow the user to make any changes to the worksheet data. All they can do is just read the report. For this, we have a built-in VBA method called “Protect.”

Like we protect our worksheets in Excel; similarly, we can use VBA to protect our worksheets. There are two methods to protect the sheet using a .protect statement. One is with a password, and another is without a password. The syntax to protect a worksheet is as follows Worksheets().Protect Password.

We usually share the end report with the user or reader. When we share the end report with the user, we wish the user would not make any modifications or manipulate the end report. It is all about trust in such a scenario.

VBA-Protect-Sheet

Syntax

The protecting sheet involves various parameters to supply. It is unlike Unprotecting the sheet. Let us look at the syntax of the Protect method with a password.

VBA Protect password formula

Don’t get intimidated by looking at the syntax. Instead, have a look at the explanation of each argument below.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

How to Protect Sheet using VBA Code?

You can download this VBA Protect Sheet Excel Template here – VBA Protect Sheet Excel Template

Step 1: Select the Sheet which needs to be protected

The first step is to decide which sheet we need to protect using a password to protect the sheet. Next, we need to call the sheet by name using the VBA Worksheet Object.

For example, assume you want to protect the “Master Sheet” sheet, then you need to mention the worksheet name below.

VBA Protect Sheet Example 1

Step 2: Define Worksheet Variable

After mentioning the worksheet name, put a dot, but we don’t see any IntelliSense list to work with. So, it makes the job difficult. To access the IntelliSense list, define the variable as a worksheet.

Code:

Sub Protect_Example1()

  Dim Ws As Worksheet

End Sub
VBA Protect Sheet Example 1-1

Step 3: Give Worksheet Reference

Now, set the worksheet reference to the variable as Worksheets(“Master Sheet”).

Code:

Sub Protect_Example1()

   Dim Ws As Worksheet

   Set Ws = Worksheets("Master Sheet")

End Sub
VBA Protect Sheet Example 1-2

Now, the variable “Ws” holds the reference of the worksheet named “Master Sheet.” By using this variable, we can access the IntelliSense list.

Example 1-3

Step 4: Select Protect Method

Select the “Protect” method from the IntelliSense list.

Example 1-4

Step 5: Enter Password

Specify the password in double-quotes.

Code:

Sub Protect_Example1()

   Dim Ws As Worksheet

   Set Ws = Worksheets("Master Sheet")

   Ws.Protect Password:="MyPassword"

End Sub
Example 1-5

Step 6: Run the Code

Run the code manually or use the shortcut key F5. Then, it will protect the sheet named “Master Sheet.”

When the sheet is protected, if we want to modify it, it shows an error message, as shown below.

VBA Protect Sheet Example 1-6

We need to use loops if you wish to protect more than one sheet. Below is the example code to protect the sheet.

Sub Protect_Example2()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
  Ws.Protect Password:="My Passw0rd"
  Next Ws

End Sub

Note: Use other parameters to experiment.

Recommended Articles

This article is a guide to VBA Protect Sheets. Here, we learn how to use Protect methods in VBA to protect or lock an Excel sheet using a password, along with a practical example and a downloadable template. Below you can find some useful Excel VBA articles: –