When we are dealing with confidential information or data we need to Protect Worksheet from the user to edit data, delete data, and move data in Excel VBA. If user wants to see data in Worksheet user should have password to view data in the Worksheet. We need to protect Worksheet at the end of the procedure once changes has done to the Worksheet using VBA. If you forgot the password, you cannot UnProtect the Worksheet.

VBA Protect Method Excel Worksheet Object

Why we use Protect Worksheet Method using VBA?

When Worksheet has sensitive data to deal with confidential information or data we need to Protect Worksheet in Excel VBA using protect method of worksheet object. You should protect your complete Worksheet then only authorized users can view or modify the data.

VBA Protect Worksheet Method – Syntax

Here is the syntax to Protect Worksheet using VBA.

Worksheets(“Your Worksheet Name”). Protect(

[Password], [DrawingObjects], [Contents], [Scenarios], [UserInterfaceOnly], [AllowFormattingCells], [AllowFormattingColumns], [AllowFormattingRows], [AllowInsertingColumns], [AllowInsertingRows], [AllowInsertingHyperlinks], [AllowDeletingColumns], [AllowDeletingRows], [AllowSorting], [AllowFiltering], [AllowUsingPivotTables])

Where
Password: Its Optional parameter. It specifies a case sensitive password for the Worksheet. If omitted then Worksheet will be locked without a password and while unprotecting it, user will not be asked for any password. When you want to unprotect your protected Worksheet you must specify your password.
DrawingObjects: Its Optional parameter and Boolean type Input. Default value is TRUE. If the value is True it allows to protect all shapes in the Sheet.
Contents: Its Optional parameter and Boolean type Input. Default Value is TRUE. If the value is TRUE to protect contents of the Worksheet except those cells or range which are set as “Un-Locked”.
Scenarios: Its Optional parameter and Boolean type Input. The default value is True. If the value is True then it protect scenarios.
UserInterfaceOnly : Its Optional parameter and Boolean type Input. If the value is True to protect the user interface, but not macros. If this parameter is omitted, protection applies both to macros and to the user interface.
AllowFormattingCells: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to format any cell on a protected worksheet.
AllowFormattingColumns: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to format any Column on a protected worksheet.
AllowFormattingRows: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to format any Row of the protected worksheet.
AllowInsertingColumns: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to insert column in the protected worksheet.
AllowInsertingRows: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to insert Rows in the protected worksheet.
AllowInsertingHyperlinks: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to insert Hyperlinks on the protected worksheet.
AllowDeletingColumns: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to delete columns from the protected worksheet.
AllowDeletingRows: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows user to delete rows from the protected worksheet.
AllowSorting: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is 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: Its Optional parameter and Boolean type Input. Default Value is FALSE. This will allow user to change the filter criteria but cannot 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: Its Optional parameter and Boolean type Input. Default Value is FALSE. If the value is TRUE then it allows the user to use pivot table reports on the protected worksheet.
In the above syntax we are using ‘Protect’ method of Worksheet object to protect the Worksheet.

VBA Protect Worksheet Method – Without Password

Please find the below example, It will show you how to protect the Worksheet without password.

Sub Protect_WorkSheet_Without_Password()
Sheets("Sheet1").Protect
End Sub

Explanation: In the above example we are protecting the Worksheet without password by using ‘Protect’ method of Worksheet object in the worksheet named ‘Sheet1’. In the above procedure we are not providing any password, so that the worksheet will be locked for editing. While unprotecting same worksheet it won’t ask any password to unprotect the worksheet.

VBA Protect Worksheet Method – With Password

Here is the example to protect Worksheet with password.

Sub Protect_WorkSheet_With_Password()
Sheets("Sheet1").Protect "YourPassword", True, True
End Sub

Explanation: In the above example we are protecting the Worksheet with password by using ‘Protect’ method of Worksheet object in the worksheet named ‘Sheet1’. When working Worksheet code has password protected, if you want to do any changes in the code first you have to unprotect the password at the beginning of the procedure and at the end of the procedure you have to protect it again. While unprotecting same worksheet it will ask for the password to unprotect the worksheet.

VBA Protect Worksheet Method: Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:

  1. Open an Excel Worksheet
  2. Press Alt+F11 :This will Open the VBA Editor. Otherwise, you can open it from the Developer Tab
  3. Insert a Module from Insert Menu
  4. Copy the above code for activating worksheet and Paste in the code window(VBA Editor)
  5. Save the file as macro enabled Worksheet
  6. Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line and check how the Worksheet has Protected.

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!