Excel Macro : Excel VBA code to Protect OR UnProtect Sheet

.

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.

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…


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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

2 Comments

  1. Jessie

    What if the whole workbook is protected, how I do I write code to make it automatically unprotect when I open the file.

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro Excel Tip : How to Make an Excel Cell secured for Password - [...] your WorkSheet with all default options selected. To know more about protecting a WorkSheet..read this article. It is recommended…
  2. Excel Tip : How to Make an Excel Cell secured for Password | Unified Networking - […] your WorkSheet with all default options selected. To know more about protecting a WorkSheet..read this article. It is recommended…
  3. VBA to Open or Create a Password Protected Workbook - Welcome to LearnExcelMacro.com - […] I have written about how to protect and un-protect a Sheet in a Workbook. In this article, I am…
  4. 40 Useful Excel Macro [VBA] examples - Part 2 of 2 [ FREE DOWNLOAD ] - […] Excel Macro : Excel VBA code to Protect OR UnProtect Sheet(Opens in a new browser tab) […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest