What is Excel VBA Protect Sheet?
Excel VBA (Visual Basic for Applications) Protect Sheet is a feature that allows you to secure and restrict access to a specific worksheet in an Excel workbook. It enables you to prevent unauthorized users from making changes to the content, structure, or formatting of the protected sheet.
You can use VBA code to protect a sheet, which includes options to specify a password and control various permissions, such as allowing or disallowing users to filter, sort, or perform other actions on the protected sheet.
In the below example, we protect an Excel worksheet using the VBA Protect Sheet with password while allowing users to filter data but disallow sorting and other changes.
Here, we use the Protect method on the worksheet variable to protect the sheet. We set a password (“YourPassword”) by passing it as a string to the Password parameter. Additionally, we enable data filtering by setting AllowFiltering to True. However, we explicitly disallow sorting by setting AllowSorting to False.
Finally, executing this VBA code will apply these protection settings to the selected worksheet. You will be able to filter the data. However, you will not be able to sort or edit the data due to the parameters we have provided.
Table of contents
Key Takeaways
- VBA Protect Sheet in Excel allows you to secure and restrict access to specific worksheets.
- You can use VBA code to protect sheets with options like passwords and permissions.
- Use the ProtectContents property to check if a worksheet is protected in VBA.
- Troubleshoot Protect Sheet issues by verifying passwords and code correctness.
- To unprotect all sheets, loop through them and use the Unprotect method.
Syntax
The basic syntax for protecting a worksheet using VBA in Excel is as follows:
Worksheets(“SheetName”).Protect Password:=”YourPassword”, _
DrawingObjects:=True/False, Contents:=True/False, Scenarios:=True/False, _
AllowFormattingCells:=True/False, AllowFormattingColumns:=True/False, _
AllowFormattingRows:=True/False, AllowInsertingColumns:=True/False, _
AllowInsertingRows:=True/False, AllowInsertingHyperlinks:=True/False, _
AllowDeletingColumns:=True/False, AllowDeletingRows:=True/False, _
AllowSorting:=True/False, AllowFiltering:=True/False
| Parameter | Description |
|---|---|
| Worksheet Name | Specify the name of the worksheet you want to protect. |
| Password | Set a password for protection. If omitted, the sheet is locked without a password, allowing easy unprotection. |
| Drawing Objects | Decide whether to protect objects in the worksheet (TRUE) or not (FALSE). Default is TRUE. |
| Contents | Choose to protect the contents of the worksheet (TRUE) or only locked cells (FALSE). Default is FALSE (locked). |
| Scenarios | Allow protection of what-if analysis scenarios in Excel (TRUE) or disallow it (FALSE). Default is TRUE. |
| User Interface Only | Determine if the protection is for the user interface only (TRUE) or includes Macros (FALSE). Default is FALSE. |
| Allow Formatting Cells | Enable or disable user formatting of cells (TRUE or FALSE). Default is FALSE (disallowed). |
| Allow Formatting Columns | Permit or prevent user formatting of columns (TRUE or FALSE). The default is FALSE (disallowed). |
| Allow Formatting Rows | Allow or disallow user formatting of rows (TRUE or FALSE). The default is FALSE (disallowed). |
| Allow Insert Columns | Allow or deny users the ability to insert new columns (TRUE or FALSE). Default is FALSE (disallowed). |
| Allow Insert Rows | Allow or deny users the ability to insert new rows (TRUE or FALSE). Default is FALSE (disallowed). |
| Allow Insert Hyperlinks | Decide whether users can insert hyperlinks (TRUE) or not (FALSE). Default is FALSE (disallowed). |
| Allow Deleting Columns | Specify if users can delete columns (TRUE) or not (FALSE). The default is FALSE (disallowed). |
| Allow Deleting Rows | Specify if users can delete rows (TRUE) or not (FALSE). The default is FALSE (disallowed). |
| Allow Sorting | Determine if users can sort data (TRUE) or not (FALSE). Default is FALSE (disallowed). |
| Allow Filtering | Decide if users can filter data (TRUE) or not (FALSE). Default is FALSE (disallowed). |
| Allow Using Pivot Tables | Allow or deny users the ability to use pivot tables (TRUE or FALSE). Default is FALSE (disallowed). |
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Protect a Sheet Using VBA Code?
Let us look at an example that demonstrates how to protect an Excel worksheet using VBA Protect Sheet with options, including setting a password, allowing or disallowing various user actions, and providing step-by-step instructions in comments.
Here are the steps for the VBA Protect Sheet in Excel:
Step 1: Select the Sheet to Be Protected
Before writing VBA code, select the worksheet you want to protect. You can do this by clicking on the sheet tab at the bottom of the Excel window. In this case, it is Sheet2.
Step 2: Define a Worksheet Variable
In the new module, declare a variable to represent the worksheet you selected. This will allow you to work with the sheet in your code.
Step 3: Give the Worksheet a Reference
Set the worksheet variable to refer to the selected worksheet.
This line of code assigns a reference to the “Sheet2” worksheet within the same workbook as the VBA code to the variable “ws,” enabling manipulation of that specific worksheet in VBA.
Step 4: Select the Protect Method
Use the VBA Protect Sheet method on the worksheet variable to protect the sheet. You can specify various options like passwords, permissions, and more using the syntax mentioned earlier.
Here, we are protecting an Excel worksheet that is represented by the variable “ws” with specific protection options. It sets a password (“YourPassword”) for protection and allows:
- Formatting of cells (AllowFormattingCells) and inserting columns (AllowInsertingColumns).
- Inserting rows in VBA (AllowInsertingRows) and filtering data (AllowFiltering).
However, it disallows:
- Formatting of columns (AllowFormattingColumns) and rows (AllowFormattingRows)
- Inserting hyperlinks (AllowInsertingHyperlinks), deleting columns (AllowDeletingColumns), and deleting rows (AllowDeletingRows)
- Sorting data (AllowSorting)
Modify the options as needed for your specific requirements.
Step 5: Enter a Password (Optional)
If you want to require a password to unprotect the sheet, include the Password parameter in the Protect method and provide the desired password as a string.
In this example, we have set the password as “YourPassword.”
Step 6: Run the Code
Execute your VBA code by pressing F5 or by running it from the VBA editor. This will protect the selected sheet with the specified settings.
Execute your VBA code by pressing F5 or by running it from the VBA editor. It will protect the selected sheet with the specified settings.
When you run this code, it protects the “Sheet2” worksheet with the password “YourPassword.” It allows formatting cells, inserting columns and rows, and filtering data while disallowing formatting of columns and rows, inserting hyperlinks, deleting columns and rows, and sorting data.
Important Things to Note
- Be cautious when using the VBA Protect Sheet, as mistakes can lead to unintended restrictions on your workbook.
- To unprotect a protected sheet, you can use the Unprotect method with the correct password or omit the password parameter if it was not set during protection.
- You can use the VBA Protect Sheet Allow Filter to enable users to filter data when protecting a sheet.
Frequently Asked Questions (FAQs)
In VBA, you can use the ProtectContents property to check if a worksheet is protected. If ProtectContents is set to True, it means the worksheet is protected, and if it’s False, the worksheet is not protected. You can use this property to conditionally check and handle protected sheets in your VBA code.
VBA Protect Sheet may not work due to various reasons:
Incorrect password: If you’ve set a password during protection and provide the wrong password in your VBA code, the protection won’t be applied or removed.
Coding errors: Mistakes in your VBA code, such as typos in sheet names or missing parameters in the Protect or Unprotect methods, can prevent the protection from working as expected.
Pre-existing protection: If the sheet is already protected when you attempt to protect it using VBA, it can lead to conflicts or errors.
To unprotect all sheets in VBA, you can create a loop that iterates through each worksheet in the workbook and uses the Unprotect method to remove protection. This loop allows you to systematically unprotect all sheets in the workbook, making them editable. It’s a useful approach when you need to remove protection from multiple sheets at once.
Download Template
This article must be helpful to understand the VBA Protect Sheet, with its syntax and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA Protect Sheet. Here we learn syntax, parameters and how to protect excel sheet using VBA code, with points to remember. You can learn more from the following articles –
Leave a Reply