This is a small VBA code to help you in dealing with Protecting and UnProtecting the WorkSheet using Excel Macro. Assuming the fact here that you know How to Protect or UnProtect a Sheet using Excel In-Built function . For those who do not know protect or unprotect the sheet using Excel inbuilt function, I will give a brief about the same at the end of this Article.

Excel Macro to Protect your Sheet

To protect an Excel WorkSheet, Worksheet.Protect Method is used. Using this method you will be able to protect your WorkSheet. Let’s see the Syntax and How to use Worksheet Protect Method in Excel Macro.

How to use Worksheet Protect Method in Excel Macro

Syntax:

Note: Like .PrintOut Method this method has all Optional parameters too.

Example 1: Protect your Sheet with All Default options

Function ProtectSheet_Default()
'To protect the Sheet1 without any password with all default parameters
    Worksheets("Sheet1").Protect
End Function

Example 2: Protect your Sheet with a password

Function ProtectSheet_Password()
'To protect the Sheet1 with a password and with all other default parameters
'Passwords are case sensitive. Hence Vishwa123 is not equal to visHwa123
    Worksheets("Sheet1").Protect Password:="Vishwa123"
End Function

Example 3: Protect your Sheet with all parameters passed in it

Function ProtectSheet_All_Parameters_passed()
'Protect Method with all the parameters passed in it
    Worksheets("Sheet1").Protect _
        Password:="Vishwa123", _
        DrawingObjects:=False, _
        Contents:=True, _
        Scenarios:=True, _
        UserInterfaceOnly:=True, _
        AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, _
        AllowInsertingColumns:=True, _
        AllowInsertingRows:=True, _
        AllowInsertingHyperlinks:=True, _
        AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, _
        AllowSorting:=False, _
        AllowFiltering:=False, _
        AllowUsingPivotTables:=False
End Function

Excel Macro to UnProtect your Protected Sheet

To Unprotect your already protected sheet, you need to use WorkSheet.unprotect method. To Unprotect a Sheet all you need to pass is the password. No other parameter required to unprotect a sheet. Password is NOT required in all case. You need to pass a correct password only when your Sheet is protected by a giving a password. It is not protected by not providing any password then no need to pass the password as well.

Syntax:

WorkSheet.Unprotect (password)

Where:

Password (Optional) : is the password by which your Sheet is protected. If your Sheet is not protected by any password then this parameter can be omitted.

Example 1: To Unprotect a WorkSheet protected by a Passwrord

Function UnProtectSheet()
'UnProtect Method with a password passed
    Worksheets("Sheet1").Unprotect ("Vishwa123")
End Function

Example 2: To Unprotect a WorkSheet which is not protected by a Password

Function UnProtectSheet()
'UnProtect Method without a password passed
    Worksheets("Sheet1").Unprotect
End Function

Special Cases:

I just thought of putting a simple note on all those scenarios which are possible while unprotecting a Sheet.

Case 1: What if I passed a Wrong Password

In this case if you will get a 1004 : Run Time Error . If you capture the Error using On Error GoTo.. statement like shown in below code, you will receive the error message like shown in below message box:

Function UnProtectSheet()
'UnProtect Method with a password passed
On Error GoTo err
    Worksheets("Sheet1").Unprotect ("vishwa123")
err:
    MsgBox err.Number & " :  " & err.Description
End Function

Case 2: What if I passed a random Password for a sheet which is protected by NO password

In such case this method will unprotect sheet without throwing any error message.

Case 3: What if I DO NOT pass any password for a Protected sheet which is protected by a Password

In such case this method will popup a excel built-in input box to enter the password to unprotect the Sheet.
i) On passing a valid password sheet will be unprotected.
ii) In case of wrong password entered in the input box, same error message will be thrown as shown in the above picture.
iii) If you cancel the input box then Sheet will not be unprotected without any Error Message.

How to Protect your Sheet using Excel Built-In function

Follow the below simple steps to Protect your Sheet
Step 1: Activate your Sheet and go to Review Tab of the Excel Ribbon
Step 2: Now Click on Protect Sheet option shown in the below Picture:

Step 3: Now a Popup Window will appear looking for Password.

Note:

1. Giving a valid password is not mandatory. You leave it blank and press OK. In that case there will be no Popup for confirmation of your entered password as shown in the Step No: 4.
2. There are so many different options available there but for now I am not going to cover them. Keep looking this space for a detailed article on the same.
 

Step 4: Re-Enter the same password (This popup will appear only if you have passed a password in the previous step)

Now you are done with Protecting your Worksheet. To confirm the same, double click on any of the cell in the Worksheet, you will get following error message:

 

How to Un-Protect your Protected WorkSheet using Excel Built-In function

As soon as you protected your WorkSheet by following the above Steps, you will see that Protect Sheet option is changed to UnProtect Sheet option as shown in the below picture:
 

 
Step 1. Click on Unprotect sheet button in your WorkSheet
Step 2. A popup will will appear asking for Password to Unlock the sheet

Note:

This popup will appear ONLY when your Sheet was protected by passing a Password. If any password is not passed while protecting the WorkSheet, this popup will not appear and your sheet will be automatically unprotected.