How to PROTECT and UNPROTECT a Sheet using VBA in Excel

In VBA, there’s the PROTECT method that you can use with a sheet. In this method, you have the option to protect a sheet, with or without a password. And you can also protect an object from the sheet. We will see all these in detail in this tutorial.

In the tutorial, we will look at how to protect and unprotect a single sheet or multiple sheets using a VBA code.

Write a VBA Code to Protect a Sheet

To protect a sheet, you need to specify the sheet first and then use the protect method. Here are the steps.

1. Use the sheets object to specify the sheet.
2. Enter the name of the sheet that you want to protect.
3. Type a dot to get the list of the methods and properties.
4. Select the project method or type it.

Sheets("Sheet1").Protect

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Write a VBA Code to Unprotect a Sheet

To protect a sheet, you need to specify the sheet first and then use the unprotect method. Here are the steps.

1. Specify the sheet using the sheet object.
2. And then, enter the name of the sheet that you want to protect.
3. Enter a dot to get the list of the methods and properties.
4. Select the “Unprotect” method or type it.

Sheets("Sheet1").Unprotect

Protect a Sheet with a Password

If you want to set a password while protecting a sheet, in that case, you need to use the password argument to specify a password. Let’s say you want to set a password “test123” to the sheet for protecting it, the code would be like the one below.

Sheets("Sheet1").Protect Password:="test123"

Unprotect a Sheet with a Password

In the same way, if you want to unprotect a sheet, you need to mention the password in the password argument. Let’s say the password that you have used to protect the sheet is “ADSBP” so the code to unprotect it would be like below.

Sheets("Sheet1").Unprotect Password:="ADSBP"

There’s one thing that you need to take care, take care of capital letter as VBA differentiate between capital and small letters.

Other Things to Know

As I said, we are using VBA’s “Protect” method, and there are arguments other than “Password” with this method that you can use.

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

1. DrawingObjects: To protect and unprotect shapes.
2. Contents: TO protect cells that are locked and the entire chart.
3. Scenarios: To protect scenarios in the worksheet.
4. UserInterfaceOnly: To only protect the user interface, not macros.
5. AllowFormattingCells: To allow the user to apply formatting to cells.
6. AllowFormattingColumns: To allow the user to apply formatting to columns.
7. AllowFormattingRows: To allow the user to apply formatting to rows.
8. AllowInsertingColumns: To allow the user to insert new columns.
9. AllowInsertingRows: To allow the user to insert new rows.
10. AllowInsertingHyperlinks: To allow the user to create hyperlinks.
11. AllowDeletingColumns: To allow the user to delete columns.
12. AllowDeletingRows: To allow the user to delete rows.
13. Allow Sorting: To allow the user to sort rows, columns, and tables.
14. AllowFiltering: To allow filtering columns.
15. AllowUsingPivotTables: To let the user use a pivot table.

Notes

• Make sure to use strong passwords that combine uppercase and lowercase letters, numbers, and symbols.
• If you forget your password, Microsoft cannot retrieve it. So, make sure to write down your password somewhere in a safe place.
• If a sheet is protected without a password, and now you want to protect it with a password, you need to unprotect it first. And then reprotect it with a password using the code you have seen above.