VBA Unprotect Sheet

Excel VBA Unprotect Sheet

We have an option in Excel by which we can protect our excel sheets and files from getting deleted, renamed and from saving the data from any other changes which are not required. The option is called Protect Sheet and Protect Workbook which is there in Changes section under Review menu ribbon. We can set a password by which we can save the selected sheet from any changes made. But what I say, this can also be automated using VBA code. Yes, we can create a code or macro by which we can automatically lock the sheet and workbook. But this article is all about Unprotecting the sheet.

Unprotecting the sheet is also quite an easy process as protecting the sheet. For unprotecting the sheet, we need to enter the password which we used while locking that sheet. For unprotecting the sheet, we just need to select the name of the worksheet and the function called Unprotect. If that is available in VBA, then it is just a one-line code. But if it is not, then we need to look for some other way of doing it which we will see in the below examples.

How to Unprotect Sheet in Excel VBA?

We will learn how to Unprotect Sheet in Excel using the VBA Code.

VBA Unprotect Sheet – Example #1

First, we would see a simple example to unprotect the sheet. For this, follow the steps below:

Step 1: First open a module from the Insert menu tab as shown below.

Step 2: Now write the subprocedure of the VBA Unprotect sheet. Or we can choose any other name to define it.

Code:

Sub VBA_Unprotect()

End Sub

Step 3: Define a variable called Worksheet by any name. Preferably the name which resembles worksheet such as ExSheet

Code:

Sub VBA_Unprotect()

Dim ExSheet As Worksheet

End Sub

Step 4: Now then Set the defined worksheet variable with the name of the Sheet which we want to unprotect. Here, the name of that sheet is Sheet1.

Code:

Sub VBA_Unprotect()

Dim ExSheet As Worksheet
Set ExSheet = Worksheets("Sheet1")

End Sub

Step 5: Now assign Unprotect function which is there in VBA drop-down list to the worksheet variable ExSheet.

Code:

Sub VBA_Unprotect()

Dim ExSheet As Worksheet
Set ExSheet = Worksheets("Sheet1")
ExSheet.Unprotect

End Sub

Step 6: Now give the password which we gave for locking that sheet. Here, that password is “Open1212”. And this can be anything, which is up to the user’s choice.

Code:

Sub VBA_Unprotect()

Dim ExSheet As Worksheet
Set ExSheet = Worksheets("Sheet1")
ExSheet.Unprotect Password:="Open1212"

End Sub

Step 7: Now first we will protect Sheet1 by clicking on the Review menu tab and selecting Protect Sheet option.

Step 8: Enter the password which we want and click on Ok to protect the sheet.

Step 9: Re-enter the password to confirm.

Step 10: Once done, we will see, we cannot change anything in that sheet even if we try to. Now to unprotect the sheet, we will compile our written code and run it.

We will see, once our code is executed, we are now able to make any changes in Sheet1.

Step 11: What if we remove or change the line of code where we have inserted the password and make it till Unprotect function as shown below. Let’s see what will happen if we do so.

Code:

Sub VBA_Unprotect()

Dim ExSheet As Worksheet
Set ExSheet = Worksheets("Sheet1")
ExSheet.Unprotect

End Sub

Step 12: Now again run the complete code. We will see, a message box will appear which will ask to enter the password. Feed the password here which we have selected while protecting the sheet and click on Ok.

With this process also, we can unprotect the sheet we want.

VBA Unprotect Sheet – Example #2

There is another way of unprotecting worksheets. This is the easiest and simplest way to do it. For this, follow the below steps:

Step 1: Write the subprocedure for VBA Unprotect as shown below.

Code:

Sub VBA_Unprotect2()

End Sub

Step 2: Use Worksheets function and select the sheet which we want to unprotect. Here again, that sheet is Sheet1.

Code:

Sub VBA_Unprotect2()

Worksheets("Sheet1")

End Sub

Step 3: In a similar way as shown in example-1, we will use Unprotect command along with the password which we used while locking the sheet. Here, our password for unprotecting is also the same as “Open1212”.

Code:

Sub VBA_Unprotect2()

Worksheets("Sheet1").Unprotect Password:="Open1212"

End Sub

Now our code is completed. To test this, first, protect the sheet in the same way as we did in example-1 and run this code to unprotect it.

VBA Unprotect Sheet – Example #3

What if I say, we can again write a code where we don’t need to select the name of the sheet which we want to unprotect. Yes, this can be done using a loop. For this, follow the below steps:

Step 1: Again for this open a Module and write the subprocedure of VBA Unprotect sheet as shown below.

Code:

Sub VBA_Unprotect3()

End Sub

Step 2: Use DIM to define a variable for Worksheet as Exsheet. It is the same line which we have used in the above examples.

Code:

Sub VBA_Unprotect3()

Dim ExSheet As Worksheet

End Sub

Step 3: Open a For-Next loop where we will be writing the unprotect sheet conditions.

Code:

Sub VBA_Unprotect3()

Dim ExSheet As Worksheet

For 

Next ExSheet

End Sub

Step 4: Write the condition for each opened active workbook selects the current active Worksheet, as shown below.

Code:

Sub VBA_Unprotect3()

Dim ExSheet As Worksheet

For Each ExSheet In ActiveWorkbook.Worksheets

Next ExSheet

End Sub

Step 5: Now in a similar manner as seen in the above examples, we will use defined variables along with Unprotect function to unprotect the sheet. Here also, we may or may not use the Password. This is an optional way.

Code:

Sub VBA_Unprotect3()

Dim ExSheet As Worksheet

For Each ExSheet In ActiveWorkbook.Worksheets
ExSheet.Unprotect Password:="Open1212"

Next ExSheet
End Sub

Compile the code and run if no error found.

This is how we can unprotect any opened current worksheet without even selecting the name and sequence of that. This code will automatically consider that sheet which is currently selected and opened.

Pros of Excel VBA Unprotect Sheet

• Above shown processes and examples are the easiest to apply.
• We can unprotect the sheet in both ways, giving the password itself in the code or without giving the password.
• This works in the same manner as we manually unprotect the sheet.

Things to Remember

• As the process is quite easy to implement, so there may not be any improvement by automating.
• We can change the password each time when we redo the process of protecting and unprotecting the sheet.
• We can select the name of the Sheet or sequence of the sheet which we want to unprotect it.
• And once done, save the code in Macro enable excel worksheet to protect the VBA Code getting lose.

Recommended Articles

This is a guide to VBA Unprotect Sheet. Here we discuss how to Unprotect Sheet in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Public Variable (Examples)
2. VBA Format Number with Function
3. VBA Variant with Data Types
4. VBA Randomize | Excel Templates
5. VBA Environ