EDUCBA

Home VBA VBA Resources VBA Information Functions VBA Unprotect Sheet
 

VBA Unprotect Sheet

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Unprotect Sheet in Excel VBA?

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

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

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.

Insert Module

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

VBA Unprotect Sheet Example 1-1

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

VBA Unprotect Sheet Example 1-2

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

VBA Unprotect Sheet Example 1-3

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

VBA Unprotect Sheet Example 1-4

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

VBA Unprotect Sheet Example 1-5

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

Select Protect Sheet option

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

VBA Unprotect Sheet Example 1-8

Step 9: Re-enter the password to confirm.

VBA Unprotect Sheet Example 1-9

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.

VBA Unprotect Sheet Example 1-10

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

VBA Unprotect Sheet Example 1-6

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.

VBA Unprotect Sheet Example 1-11

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

VBA Unprotect Sheet Example 2-1

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

VBA Unprotect Sheet Example 2-2

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

VBA Unprotect Sheet Example 2-3

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

VBA Unprotect Sheet Example 3-1

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

Use DIM Example 3-2

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

For-Next loop Example 3-3

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

Active Worksheet Example 3-4

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

Defined variables Example 3-5

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
  2. VBA Format Number
  3. VBA Variant
  4. VBA Randomize