VBA & Macro Problems

How to Allow Excel Table Filtering on a VBA Protected Worksheet

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to allow data filtering on an Excel table while maintaining worksheet protection applied via a VBA script.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Securing a worksheet using VBA code but wanting to retain the users' ability to use AutoFilter on specific tables.
Observed behavior: Standard VBA worksheet protection completely disables table filtering by default, even if the header row of the table remains unlocked.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet and that your table already has the AutoFilter drop-down arrows turned on before running the protection script.

Solution 1Recommended

Use the AllowFiltering Argument in Your VBA Code

Modify your Worksheet.Protect method in VBA to explicitly grant filtering permissions while applying cell protection.

By default, the VBA .Protect method locks down most interactive features of a worksheet, including filtering. To bypass this for filters, you can pass a specific Boolean argument to the method.

Open the VBA Editor

Open your workbook and press Alt + F11 to launch the Visual Basic for Applications (VBA) Editor.

Locate the Protection Script

Find the module, userform, or worksheet code where your sheet protection macro is written.

Add the AllowFiltering Parameter

Modify the protection line by appending AllowFiltering:=True to the parameters. For example: ActiveSheet.Protect Password:="your_password", AllowFiltering:=True.

Run the Macro

Save your code and run the macro. The worksheet will now be protected, but users will still be able to click the filter arrows to sort and filter the data.

AutoFilter State Prerequisite: The AutoFilter drop-down arrows must already be visible on the table headers before the protection macro runs. If they are disabled before protection, users cannot enable them afterward.

Advanced Sheet Protection in WPS

Protect Worksheets and Allow Filtering seamlessly with WPS Spreadsheet

WPS Office provides advanced Spreadsheet features, including comprehensive macro/VBA support, allowing you to secure your data via code or a simple user interface without sacrificing usability.

1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and navigate to the 'Review' tab on the top ribbon.
2. Configure Sheet Protection: Click 'Protect Sheet' to open the manual protection dialog box.
3. Enable Filtering Permissions: In the 'Allow all users of this worksheet to:' list, check the box for 'Use AutoFilter', set your password, and click OK. Alternatively, run your exact VBA script in the WPS Macro editor.

Fully compatible with Microsoft Excel macros and VBA scripts (.xlsm formats)Granular protection settings easily accessible via the UI or VBA codeLightweight, fast spreadsheet processing for large data sets

Frequently Asked Questions

Why can't I filter my table even after adding AllowFiltering:=True?

If the AutoFilter was not active (the drop-down arrows were not visible) before the worksheet was protected, the AllowFiltering:=True parameter will not allow users to create a new filter. You must turn on the filter before executing the protection VBA code.

Can I also allow users to sort data in a protected sheet using VBA?

Yes. Similar to filtering, you can add the AllowSorting:=True argument to your .Protect method. However, keep in mind that the cells being sorted must also be unlocked for the sorting operation to complete successfully.

Does the AllowFiltering VBA script work in WPS Spreadsheet?

Yes, WPS Spreadsheet supports standard VBA methods for worksheet protection. Your ActiveSheet.Protect AllowFiltering:=True script will work perfectly as long as you have the VBA module enabled in your WPS Office version.

Excel & Data Problems