Hello Friends,

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 does 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.

Syntax:

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

WorkSheet.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

Where:

  • Password: (Optional) is the password you want to pass to protect your sheet. If omitted then WorkSheet will be locked without a password and while unprotecting it, user will not be asked for any password.
  • DrawingObjects: (Optional) This is Boolean type input. Default value is TRUE. True allows to protect all shapes in the Sheet.
  • Contents: (Optional) This is Boolean type input. Default Value is TRUE. This is Set to TRUE to protect contents of the Worksheet except those cells or range which are set as “Un-Locked”.
  • Scenarios: (Optional) This is Boolean type input. True is to protect scenarios. The default value is True.
  • UserInterfaceOnly: (Optional) This is Boolean type input. This is set to True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.
  • AllowFormattingCells: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows user to format any cell on a protected worksheet.
  • AllowFormattingColumns: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows user to format any Column on a protected worksheet.
  • AllowFormattingRows: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows user to format any Row of the protected worksheet.
  • AllowInsertingColumns: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows user to insert column in the protected worksheet.
  • AllowInsertingRows: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows user to insert Rows in the protected worksheet.
  • AllowInsertingHyperlinks: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows user to insert Hyperlinks on the protected worksheet.
  • AllowDeletingColumns: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows user to delete columns from the protected worksheet.
  • AllowDeletingRows: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows user to delete rows from the protected worksheet.
  • AllowSorting: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows user to sort data in the protected worksheet. Note:To apply sort every cell in the range must be unlocked.
  • AllowFiltering: (Optional) This is a Boolean type Input. Default Value is FALSE. This will allow user to change the filter criteria but can not enable or disable the AUTO-FILTER option in worksheet. Users are allowed just to change the different filters on an already existing auto-filter.
  • AllowUsingPivotTables: (Optional) This is a Boolean type Input. Default Value is FALSE. If you set it to TRUE then it allows the user to use pivot table reports on the protected worksheet.

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

Unprotect-Sheet - Error-Message

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.

Now you know to use .Protect and .Unprotect method to protect and unprotect a WorkSheet using Excel VBA

 

*****************************************************************

 

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:
Protect-Worksheet
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.
 
Protect-Sheet-Password
Step 4: Re-Enter the same password (This popup will appear only if you have passed a password in the previous step)
Protect-Sheet-Confirm-Password

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:

 
Protected-Sheet-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:
 
Unprotect - Worksheet
 
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.
 
Unprotect-Sheet-password

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!