FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA UnProtect Sheet

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

Excel VBA Unprotect Sheet

There are situations where we protect our worksheets to prevent the user from manipulating the worksheets. However, while protecting the worksheet, we usually maintain the same password. In this article, we will show you the way to unprotect the sheet using VBA coding.

VBA-Unprotect-Sheet

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA UnProtect Sheet (wallstreetmojo.com)

Examples

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

Example #1

Unprotecting the sheet is 99% simpler than protecting them. We need the password to unprotect the worksheet.

The first thing we need to do is mention the worksheet name we will unprotect. For example, if you are looking to unprotect the “Sales Data” sheet, your VBA code should be like this.

Worksheets(“Sales Data”)

Then, access the UnProtect method.

VBA Unprotect sheet Example 1

As shown in the above image, we don’t see any IntelliSense list to assist us. It makes the job tougher, especially for the new learner.

To overcome this, we usually declaring a VBA variable as a “Worksheet.”

Code:

Sub Unpretect_Example1()

   Dim Ws As Worksheet

End Sub
VBA Unprotect sheet Example 1-1

Now, set the declared variable to the respective sheet.

Code:

Sub Unpretect_Example1()

   Dim Ws As Worksheet

   Set Ws = Worksheets("Sales Data")

End Sub
VBA Unprotect sheet Example 1-2

Now, use a variable to access all the properties and methods of the declared variable.

VBA Unprotect sheet Example 1-3

As shown in the above image, we can access all the properties and methods. First, select the “Unprotect” method from the IntelliSense list.

VBA Unprotect sheet Example 1-4

It is asking for the password to unprotect the Sheet. We have set the password as “Excel@1234,” so we will supply the password as the same.

Example 1-5

It will unprotect the worksheet named “Sales Data.”

Code:

Sub Unpretect_Example1()

   Dim Ws As Worksheet

   Set Ws = Worksheets("Sales Data")

   Ws.Unprotect Password:="Excel@1234"

End Sub

If there is no password, we need to use the “Unprotect ” method and ignore the “Password” parameter.

Code:

Sub Unpretect_Example1()

   Dim Ws As Worksheet

   Set Ws = Worksheets("Sales Data")
  'Change the worksheet name as per your requirement

   Ws.Unprotect
  'If there is no password just pass "UnProtect" method

End Sub

If there is any password, then we need to enter the password in double quotes. The password is case sensitive so remember them carefully.

–>> 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.

Example #2 – Unprotect All Excel Sheets with Just a Click

We have seen how to unprotect the particular worksheet. However, imagine you have many worksheets. Then, it will protect all the worksheets with the same password. But, then, we cannot keep writing codes for each worksheet separately.

In these cases, we need to use loops to loop through the worksheet object collection and unprotect them easily.

The below code will loop through all the worksheets and unprotect the sheet.

Code:

Sub Unpretect_Example2()

   Dim Ws As Worksheet

   For Each Ws In ActiveWorkbook.Worksheets
        Ws.Unprotect Password:="Excel@1234"
        'Change the password as you have mentioned while protecting them.
    Next Ws

End Sub
Example 1-6

Example #3 – Special Situations

Situation 1: Have you ever imagined when the supplied password was wrong? When the supplied password is wrong, we will get a 1004: Run Time Error.

VBA Unprotect sheet Example 3-4

We can use the “On Error GoTo Label” option to handle these errors. The below code is an example of the same.

Code:

Sub Unpretect_Example3()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
        On Error GoTo Errormessage
        Ws.Unprotect Password:="Excel@1234"
        'Change the password as you have mentioned while protecting them.
Errormessage:
          MsgBox "Wrong Password"
    Next Ws
End Sub

The above code will show a nice message box saying, “The wrong Password.”

VBA Unprotect sheet Example 3-2

Situation 2: When the sheet protects without a password, and if you supply a random password, it will still unprotect the worksheet without any errors.

Situation 3: When a password protects the sheet, but if you don’t supply any password, then VBA will pop up as a password input box to enter the password.

Example 3-3

In the above input box, we need to enter our password to unprotect the worksheet. If you click on the Cancel button, it will exit the VBA subprocedure without showing anything unless there is any user message box.

Recommended Articles

This article is a guide to VBA UnProtect Sheet. Here we learn how to use VBA code to unprotect the Excel sheet with passwords and some simple to advanced examples. Below are some useful Excel articles related to VBA: –