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.
- Use the sheets object to specify the sheet.
- Enter the name of the sheet that you want to protect.
- Type a dot to get the list of the methods and properties.
- Select the project method or type it.
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.
- Specify the sheet using the sheet object.
- And then, enter the name of the sheet that you want to protect.
- Enter a dot to get the list of the methods and properties.
- Select the “Unprotect” method or type it.
Protect a Sheet with 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 if you want to set a password “test123” to the sheet for protecting it, the code would be like the below.
Unprotect a Sheet with 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.
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)
- DrawingObjects: To protect and unprotect shapes.
- Contents: TO protect cells that are locked and the entire chart.
- Scenarios: To protect scenarios in the worksheet.
- UserInterfaceOnly: To only protect the user interface not macros.
- AllowFormattingCells: To allow the user to apply formatting to cells.
- AllowFormattingColumns: To allow the user to apply formatting to columns.
- AllowFormattingRows: To allow the user to apply formatting to rows.
- AllowInsertingColumns: To allow the user to insert new columns.
- AllowInsertingRows: To allow the user to insert new rows.
- AllowInsertingHyperlinks: To allow the user to create hyperlinks.
- AllowDeletingColumns: To allow the user to delete columns.
- AllowDeletingRows: To allow the user to delete rows.
- Allow Sorting: To allow the user to sort rows, columns, and tables.
- AllowFiltering: To allow filtering columns.
- AllowUsingPivotTables: To let the user use a pivot table.
- 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 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.
More Tutorials on VBA Worksheets
- How to Insert a New Sheet using VBA
- How to Activate a Sheet using VBA
- How to Copy a Sheet using VBA
- How to Rename a Sheet using VBA
- How to Hide a Sheet using VBA
- How to Delete a Sheet using VBA
- How to Count Sheets using VBA
- How to Clear an Entire Sheet using VBA
- How to Check is a Sheet Exists using VBA
- How to Loop Through Sheets in a Workbook
- Back to VBA Worksheet / VBA Tutorial