VBA Unprotect Sheet

What is Excel VBA Unprotect Sheet?

A worksheet in Excel can be protected to stop unauthorized users from making changes to it. Programmatically, unprotecting a protected sheet is possible with Excel VBA. It comes in especially handy when you need to automate processes, unlock sheets that have lost their passwords, or make changes to a protected worksheet. You can remove protection from a worksheet using “The VBA Unprotect Sheet” method, with or without a password.

Let us look at an example to show you how to use VBA to unlock the current workbook’s ‘Sheet1’ Excel worksheet.

VBA Unprotect Sheet - Definition Example - 1

The macro below applies the VBA Unprotect Sheet method to the worksheet “Sheet1” in the current workbook, where the Password parameter is set to “MySecretPassword.” In essence, this code is meant to undo the protection on the “Sheet1” worksheet if the password that is entered, “MySecretPassword,” is the same as the password that was originally set. When the correct password is entered, this code essentially unlocks “Sheet1,” enabling programmatic changes to its contents.

VBA Unprotect Sheet - Definition Example - 2

Running this VBA code will, if the password is entered correctly, remove the protection from ‘Sheet1,’ allowing you to edit and change the sheet’s contents programmatically.

VBA Unprotect Sheet - Definition Example - 3

What is Excel VBA Unprotect Sheet?

VBA Unprotect Sheet Without Password allows you to remove protection from an Excel sheet without the need to provide a password. It’s useful for situations where the password is known, or the sheet was never password-protected.
The VBA Unprotect Sheet with Unknown Password method is helpful if you’ve forgotten the sheet’s password or are dealing with multiple sheets of unknown passwords, as it can be used to automate the removal of protection without providing passwords explicitly.
Be cautious when using hardcoded passwords in your code and consider other security measures for your workbooks.

How to Use VBA Unprotect Sheet?

To use VBA to unprotect a sheet in Excel, follow these steps:
Insert a new module by clicking on “Insert” in the menu and selecting “Module.”
To remove the protection from the sheet, use the Unprotect method in the code window.
With or without a password, you can use it:

• Without Password: To unprotect without specifying a password, use the following code.

Sub UnprotectSheetWithoutPassword() ThisWorkbook.Sheets("SheetName").Unprotect End Sub

• With Password: To unprotect with a known password, use the following code:

Sub UnprotectSheetWithPassword() ThisWorkbook.Sheets("SheetName").Unprotect Password:="YourPassword" End Sub
Change the code so that it corresponds with the name of the sheet you wish to unlock and, if necessary, the password.
Close the VBA editor and run the macro by pressing ALT + F8, selecting the appropriate macro from the list, and clicking “Run.”

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples

Example #1

Assume that the workbook you have has a protected sheet called “Data.” To remove the password-protected version, we use the VBA Unprotect Sheet method.

Step 1: In the new module, we first create a new subroutine called “UnprotectDataSheet.”

Step 2: In this step, we specify the sheet to unprotect.

The workbook containing the VBA code is referred to as “ThisWorkbook.”

“.Sheets(“Data”)” designates the particular sheet that will be left unprotected. It’s the “Data” sheet in this instance. The process of removing protection from the designated sheet is done using “.unprotect” method.

VBA Unprotect Sheet in Excel - Example 1 - Step 2

Step 3: Now save the VBA macro and click on Run.

If the sheet was previously protected and you provide the correct password (if password protection was used), the protection will be removed upon code execution, enabling you to edit the sheet.

Here is the full code:

Sub UnprotectDataSheet()
ThisWorkbook.Sheets(“Data”).Unprotect
End Sub

Example #2

If you want to unprotect all sheets in a workbook simultaneously, you can use the VBA Unprotect Sheet as follows:

Step 1: Firstly, we start by creating a new subroutine called “UnprotectAllSheets.”

Step 2: In this step, we declare a variable named “ws” as a Worksheet data type. It will be used to loop through each worksheet in the workbook.

Step 3: Now, start a loop to iterate through each sheet in the workbook.

Step 4: Inside the loop, use ws.Unprotect to remove the protection from the current sheet (ws).

Step 5: Finally, close the loop. It will continue until all sheets in the workbook have been unprotected.

Step 6: Save the macro and run the code

When this macro is executed, it will systematically unprotect all sheets in the workbook, making them available for editing.

Excel VBA Unprotect Sheet - Example 2 - Step 6a

Excel VBA Unprotect Sheet - Example 2 - Step 6b

Here is the full code:

Sub UnprotectAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
ws.Unprotect
Next ws
End Sub

Example #3

If you would like to try to unprotect the sheet but don’t know the password, we will see how to use the VBA Unprotect sheet method in this example:

Step 1: In the new module, we begin by creating a new subroutine called “AttemptUnprotectWithoutPassword.”

Step 2: In this step, we declare a variable named ws as a Worksheet data type, just like in Example #2.

Step 3: Now, start a loop to iterate through each sheet in the workbook.

Step 4: Here, we try to unprotect each sheet without a password by using error handling in VBA. Errors are momentarily ignored when using “On Error Resume Next”.

Without providing a password, “ws.Unprotect” is used to try to remove the protection from the current sheet (ws).

Error handling is returned to normal when “On Error GoTo 0″ is used.

Step 5: Now, we will close the loop, and it will continue until all sheets in the workbook have been processed.

Step 6: Save the macro and click on Run. When you run this code, it attempts to unprotect all sheets without specifying a password. If the sheet is unprotected, it proceeds; otherwise, it moves to the next sheet.

Excel VBA Unprotect Sheet - Example 3 - Step 6a

Excel VBA Unprotect Sheet - Example 3 - Step 6b

Here is the full code:

Sub AttemptUnprotectWithoutPassword()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
On Error Resume Next
ws.Unprotect
On Error GoTo 0
Next ws
End Sub

Important Things to Note

Use caution when utilizing the VBA Unprotect Sheet method as it eliminates protection without requesting confirmation.
A “Run-time error” will appear if you try to unprotect a sheet using the incorrect password. When using the Password parameter, make sure you have the correct password.
There may be security risks when using VBA to unlock sheets, particularly if you have hardcoded the password into your code. Make sure your VBA code is protected to avoid unwanted access.
In situations where you’ve forgotten the password to a protected Excel sheet, VBA offers a means to unlock it. You can employ VBA Unprotect Sheet Forgot Password to systematically unprotect sheets, allowing you to regain access to your data even when the passwords have been lost or forgotten.

Frequently Asked Questions (FAQs)

1. How can I protect and unprotect sheets in a workbook with VBA events, such as workbook open or before close?

Workbook_Open and Workbook_BeforeClose are two examples of VBA events that you can use to automatically protect and unprotect sheets when the workbook is opened or closed. For instance, you can write code to protect sheets when they are opened in the Workbook_Open event and code to unprotect them before they are closed in the Workbook_BeforeClose event.

2. Is there a way to unprotect a sheet in Excel VBA without specifying the password?

Yes, you can use the Unprotect method without the Password parameter to try to unprotect a sheet without providing the password. This will only function in the event that the sheet is password-free. You’ll get a “Run-time error” if a password is set if you don’t enter the right password.

3. Why am I getting a “Run-time error” when trying to unprotect a sheet in Excel VBA?

You get a “Run-time error” when something goes wrong with your VBA code. Entering the wrong password is the most frequent reason for this error when attempting to unprotect a sheet. Verify again that the password you entered in your code is correct.

4. Can I protect specific elements of a sheet, like cells or formatting, using VBA?

Yes, you can protect particular sheet elements with VBA. Individual cells, rows, columns, and formatting elements like charts, pictures, and shapes can all have protection applied to them. This enables you to alter the degree of security for various sections of your worksheet.

Download Template

This article must be helpful to understand the VBA Unprotect Sheet, with its features and examples. You can download the template here to use it instantly.

VBA Unprotect Sheet Download

VBA Unprotect Sheet