Excel VBA: Protect Sheet with Password and Allow Filter

In this article, I’ll show you how can protect an Excel sheet with a password and also allow the filter there using VBA.

Excel VBA: Protect Sheet with Password and Allow Filter (Quick View)

Sub Protect_Sheet_with_Password_and_Allow_Filter()

SheetName = "Sheet1"
Password = "ExcelDemy"

Worksheets(SheetName).Protect Password:=Password, AllowFiltering:=True

Worksheets(SheetName).EnableSelection = xlUnlockedCells

End Sub

An Overview of the VBA Code to Protect Sheet with Password and Allow Filter in Excel (Step-by-Step Analysis)

So, without further delay, let’s go to our main discussion today. We’ll break down the VBA code step-by-step to learn how to protect an Excel sheet with a password and allow the filter there.

⧪ Step 1: Inserting the Necessary Inputs

At the very outset of the code, we have to insert the necessary inputs required in this code. They are:

• The Worksheet Name
• The Password

SheetName = "Sheet1"
Password = "ExcelDemy"

⧪ Step 2: Setting the Password and Allowing the Filter

This is the most important step. We’ll set the password of the worksheet and allow the filter here.

Worksheets(SheetName).Protect Password:=Password, AllowFiltering:=True

⧪ Step 3: Setting EnableSelection to True

Next, we have to set the EnableSelection property of the worksheet to xlUnlockedCells.

Worksheets(SheetName).EnableSelection = xlUnlockedCells

So the complete VBA code will be:

⧭ VBA Code:

Sub Protect_Sheet_with_Password_and_Allow_Filter()

SheetName = "Sheet1"
Password = "ExcelDemy"

Worksheets(SheetName).Protect Password:=Password, AllowFiltering:=True

Worksheets(SheetName).EnableSelection = xlUnlockedCells

End Sub

Developing the Macro to Protect the Sheet with a Password and Allow Filter

We’ve seen the step-by-step analysis of the code to protect an Excel sheet with a password and allow the filter.

Now, we’ll see how we can develop a Macro using the code.

⧪ Step 1: Opening the VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.

⧪ Step 3: Putting the VBA Code

This is the most important step. Insert the given VBA code in the module.

⧪ Step 4: Running the Code

Click on the Run Sub / UserForm tool from the toolbar above.

The code will run. Once the code is run, the sheet will become protected with the given password. Now if you come back to the worksheet, you’ll find you can’t edit it. You’ll need the password to edit it.

Things to Remember

The moment you run the code, the worksheet will be protected with a password. You can’t edit it anymore unless you know the password. Therefore, it’s recommended not to run the code before you understand every bit of the code and have a clear idea of how to recover a protected sheet in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Therefore, this is the process of developing a macro to protect a sheet with a password and allow the filter in Excel. Do you have any questions? Feel free to ask us, we will get back to you ASAP.

Related Articles

• How to Unprotect Excel Sheet with Password Using VBA
• Excel VBA: Unprotect All Sheets
• Excel VBA: Protect Workbook with Password
• Excel VBA: Unprotect Workbook with Password
• Excel VBA: Unprotect Workbook without Password
• Excel VBA to Protect Sheet but Allow to Select Locked Cells
• Excel VBA: How to Unprotect Excel Sheet Without Password