• Login
  • Courses
  • Books
  • Cheat Sheets
  • Tutorials Archive
  • VBA Code Generator
  • Contact

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VBA Protect Sheet Allow Filter in 2 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

Excel VBA Protect Sheet Allow Filter in 2 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)In this Excel VBA Protect Sheet Allow Filter Tutorial, you learn how to:

  • Protect a sheet; and
  • Allow the user to filter (with a previously-enabled filter);

Using Excel macros.

This Excel VBA Protect Sheet Allow Filter Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

The VBA code in the Excel workbook that accompanies this Excel VBA Protect Sheet Allow Filter Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section below.

Table of Contents

  • Related Excel Macro and VBA Training Materials and Resources
  • The VBA Protect Sheet Allow Filter Snippet Template/Structure
  • The Example Before VBA Protect Sheet Allow Filter
  • Step 1: Refer to Sheet
    • Step 1 Example
  • Step 2: Protect Sheet and Allow Filter
    • Step 2 Example
  • Download the VBA Protect Sheet Allow Filter Example Workbook
  • Related Excel Macro and VBA Training Materials and Resources

Related Excel Macro and VBA Training Materials and Resources

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents below.

  • Tutorials about general macro and VBA constructs and structures:
    • Tutorials for Beginners:
      • Excel Macros: Click here to open.
      • Excel VBA: Click here to open.
    • Enable macros in Excel: Click here to open.
    • Work with the Visual Basic Editor (VBE): Click here to open.
    • Create Sub procedures: Click here to open.
    • Refer to objects (click here to open), including sheets (click here to open).
    • Work with:
      • Methods: Click here to open.
      • Loops: Click here to open.
  • Tutorials with practical VBA applications and macro examples:
    • Activate workbook: Click here to open.
    • Create new workbook: Click here to open.
    • Open workbook: Click here to open.
    • Delete sheet: Click here to open.
    • AutoFilter: Click here to open.

This Excel VBA Protect Sheet Allow Filter Tutorial is part of a more comprehensive series of Excel VBA Protect or Unprotect Sheet Tutorials.

  • Excel VBA Protect Sheet Without Password in 2 Easy Steps: Click here to open.
  • Excel VBA Unprotect Sheet Without Password in 2 Easy Steps: Click here to open.
  • Excel VBA Protect Sheet with Password in 2 Easy Steps: Click here to open.
  • Excel VBA Unprotect Sheet with Password in 2 Easy Steps: Click here to open.
  • Excel VBA Protect Sheet Allow Select Locked Cells in 4 Easy Steps: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

  • Premium Courses at the Power Spreadsheets Academy: Click here to open.
  • Books at the Power Spreadsheets Library: Click here to open.
  • VBA Cheat Sheets: Click here to open.

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

  • Code generators.
  • An extensive code library.
  • The ability to create your own code library.
  • Advanced coding tools.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

The VBA Protect Sheet Allow Filter Snippet Template/Structure

The following is the VBA protect sheet allow filter snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-sheet-allow-filter/
WorkbookObjectReference.WorksheetObjectReference.Protect AllowFiltering:=True


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

The Example Before VBA Protect Sheet Allow Filter

This Excel VBA Protect Sheet Allow Filter Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

The VBA code in the Excel workbook that accompanies this Excel VBA Protect Sheet Allow Filter Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section above.

The example worksheet (named “Protect Sheet Allow Filter”) has 1 table (cells A6 to J26) with the following characteristics:

  • 10 columns (Column 1 to Column 10).
  • 1 header row (row 6).
  • 20 entries (rows 7 to 26).
  • Filtering is enabled (dropdown arrows are displayed in the column headers).

This is the sheet the VBA protect sheet allow filter example macro I create (by following the step-by-step process below) works with. In other words: The VBA protect sheet allow filter example macro:

  • Protects this sheet; and
  • Allows the user to filter.

The image below displays the example worksheet before I execute the VBA protect sheet allow filter example macro.

Before I execute the VBA protect sheet allow filter example macro, the example worksheet is unprotected. Notice the Protect Sheet button (inside the Protect group of commands) in the Excel Ribbon (indicating the example worksheet is (currently) unprotected).

Example Excel workbook before VBA protect sheet allow filter example macro


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

Step 1: Refer to Sheet

Refer to the sheet you want to protect while allowing the user to filter.

In other words: Create a VBA expression that returns a Worksheet object representing the applicable sheet (you want to protect while allowing the user to filter).

Consider explicitly including the following references to create a fully qualified object reference returning the applicable Worksheet object:

  1. A reference to the applicable workbook. The following VBA constructs (among others) may return a Workbook object:
    • The Application.ThisWorkbook property.
    • The Application.Workbooks and Workbooks.Item properties.
    • The Application.ActiveWorkbook property.
  2. A reference to the applicable worksheet. The following VBA constructs (among others) may return a Worksheet object:
    • The Workbook.Sheets and Sheets.Item properties.
    • The Workbook.Worksheets and Worksheets.Item properties.
    • The Application.ActiveSheet property.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-sheet-allow-filter/
WorkbookObjectReference.WorksheetObjectReference

The protect sheet allow filter VBA code template/structure you learn in this Tutorial assumes the sheet you want to protect (while allowing the user to filter) is (currently) unprotected.


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

Step 1 Example

I:

  • Refer to the worksheet named “Protect Sheet Allow Filter” inside the workbook where the procedure is stored.
  • Work with the following VBA constructs to obtain a Worksheet object representing this worksheet:
    • The Application.ThisWorkbook property: ThisWorkbook.
    • The Workbook.Worksheets and Worksheets.Item properties: Worksheets(“Protect Sheet Allow Filter”).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-sheet-allow-filter/
ThisWorkbook.Worksheets("Protect Sheet Allow Filter")


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

Step 2: Protect Sheet and Allow Filter

Do the following to protect the sheet (you refer to in step #1) while allowing the user to filter.

(1) Start with the Worksheet object reference you created in step #1.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-sheet-allow-filter/
WorkbookObjectReference.WorksheetObjectReference

(2) Call the Worksheet.Protect method.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-sheet-allow-filter/
WorkbookObjectReference.WorksheetObjectReference.Protect

(3) Set the AllowFiltering parameter of the Worksheet.Protect method to True.

Setting the AllowFiltering parameter of the Worksheet.Protect method to True:

  • Allows the user to work with (change the criteria of) a previously-enabled filter in the protected worksheet; but
  • Doesn't allow the user to enable or disable filtering.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-sheet-allow-filter/
WorkbookObjectReference.WorksheetObjectReference.Protect AllowFiltering:=True


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

Step 2 Example

I do the following:

(1) Start with the Worksheet object reference I created in step #1.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-sheet-allow-filter/
ThisWorkbook.Worksheets("Protect Sheet Allow Filter")

(2) Call the Worksheet.Protect method, and set its AllowFiltering parameter to True.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/protect-sheet-allow-filter/
ThisWorkbook.Worksheets("Protect Sheet Allow Filter").Protect AllowFiltering:=True

The full VBA protect sheet allow filter example macro is as follows:

Sub ProtectSheetAllowFilter()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/protect-sheet-allow-filter/
    
    'Do the following:
        'Step 1: Refer to the "Protect Sheet Allow Filter" worksheet in this workbook
        'Step 2: Protect the sheet, while allowing the user to filter with a previously-enabled filter
    ThisWorkbook.Worksheets("Protect Sheet Allow Filter").Protect AllowFiltering:=True

End Sub

The GIF below illustrates the effects of using the VBA protect sheet allow filter example macro.

Notice how:

  • The Protect Sheet button (inside the Protect group of commands) in the Excel Ribbon (indicating the example worksheet is (currently) unprotected) is replaced by the Unprotect Sheet button (indicating the example worksheet is now protected) when I execute the VBA protect sheet allow filter example macro.
  • I can work with the (previously-enabled) filter while the worksheet is protected.
Example: Protect Excel sheet and allow filter with VBA macros


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

Download the VBA Protect Sheet Allow Filter Example Workbook

This Excel VBA Protect Sheet Allow Filter Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VBA Protect Sheet Allow Filter workbook example

The VBA code in the Excel workbook that accompanies this Excel VBA Protect Sheet Allow Filter Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section above.

Related Excel Macro and VBA Training Materials and Resources

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents above.

  • Tutorials about general macro and VBA constructs and structures:
    • Tutorials for Beginners:
      • Excel Macros: Click here to open.
      • Excel VBA: Click here to open.
    • Enable macros in Excel: Click here to open.
    • Work with the Visual Basic Editor (VBE): Click here to open.
    • Create Sub procedures: Click here to open.
    • Refer to objects (click here to open), including sheets (click here to open).
    • Work with:
      • Methods: Click here to open.
      • Loops: Click here to open.
  • Tutorials with practical VBA applications and macro examples:
    • Activate workbook: Click here to open.
    • Create new workbook: Click here to open.
    • Open workbook: Click here to open.
    • Delete sheet: Click here to open.
    • AutoFilter: Click here to open.

This Excel VBA Protect Sheet Allow Filter Tutorial is part of a more comprehensive series of Excel VBA Protect or Unprotect Sheet Tutorials.

  • Excel VBA Protect Sheet Without Password in 2 Easy Steps: Click here to open.
  • Excel VBA Unprotect Sheet Without Password in 2 Easy Steps: Click here to open.
  • Excel VBA Protect Sheet with Password in 2 Easy Steps: Click here to open.
  • Excel VBA Unprotect Sheet with Password in 2 Easy Steps: Click here to open.
  • Excel VBA Protect Sheet Allow Select Locked Cells in 4 Easy Steps: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

  • Premium Courses at the Power Spreadsheets Academy: Click here to open.
  • Books at the Power Spreadsheets Library: Click here to open.
  • VBA Cheat Sheets: Click here to open.

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

  • Code generators.
  • An extensive code library.
  • The ability to create your own code library.
  • Advanced coding tools.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

guest
guest
2 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure

Copyright © 2015–2023 PDS Intelligence Pte. Ltd. All rights reserved.
Excel ® is a registered trademark of the Microsoft Corporation. Power Spreadsheets is not affiliated with the Microsoft Corporation.