Excel VBA: Protect Sheet with Password and Allow Filter

Get FREE Advanced Excel Exercises with Solutions!

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

VBA Code to Protect Excel Sheet with Password and Allow Filter


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"

Inserting Inputs to Protect Sheet with Password and Allow Filter

⧪ 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

Setting Password to Protect Sheet with Password and Allow Filter

⧪ 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

VBA Code to Protect Excel Sheet with Password and Allow Filter


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.

Opening VBA Window to Protect Sheet with Password and Allow Filter

⧪ 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.

Putting VBA Code to Protect Sheet with Password and Allow Filter

⧪ 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo