WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA UnProtect Sheet

VBA UnProtect Sheet

Excel VBA Unprotect Sheet

There are situations where we protect our worksheets to prevent it from the user from manipulating the worksheets. 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

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 just need the password to unprotect the worksheet.

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

Worksheets(“Sales Data”)

Then access the Unprotect method.

VBA Unprotect sheet Example 1

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

To overcome this, I usually rely on declaring a VBA variable as “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 you can see in the above image, we can access all the properties and methods. Select the “Unprotect” method from the IntelliSense list.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA Unprotect sheet Example 1-4

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

Example 1-5

This 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, then we just need to use the method “Unprotect” 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.

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

We have seen how to unprotect the particular worksheet. Imagine you have many worksheets, and all the worksheets are protected with the same password, then we cannot keep writing codes for each worksheet separately.

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

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 ever imagined when the supplied password is wrong. When the supplied password is wrong, we will get the 1004: Run Time Error.

VBA Unprotect sheet Example 3-4

To handle these errors, we can use the “On Error GoTo Label” option. 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 is protected without a password, and if you supply a random password, it will still unprotect the worksheet without showing any kind of errors.

Situation 3: When the sheet is protected by a password, 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 if there is any user message box.

Recommended Articles

This has been a guide to VBA UnProtect Sheet. Here we learn how to use VBA code to unprotect the excel sheet with password along with some simple to advanced examples. Below are some useful excel articles related to VBA –

  • VBA ISERROR Function
  • VBA Solver Function
  • What is Screen Updating in VBA?
  • ThisWorkbook in VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Unprotect Sheet Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More