VBA & Macro Problems

How to Add Rows to a Protected Excel Table While Allowing Sorting and Filtering via VBA

Ayan Masood Oct 22, 2026 869 views

Question details

The user needs to modify a VBA macro so that rows can be added to an Excel table on a protected worksheet, while still permitting users to sort and filter the data.

How to Add Rows to a Protected Excel Table While Allowing Sorting and Filtering via VBA

Product: Excel
Device & OS: not provided
Scenario: Running a VBA macro to insert rows into a protected sheet containing an Excel table without locking the ability to sort and filter records.
Observed behavior: By default, applying worksheet protection via VBA removes the ability to sort and filter unless these permissions are explicitly passed as arguments in the code.

Before you start

Ensure you have the worksheet protection password available and that the Developer tab is enabled in your ribbon to edit your VBA macros.

Solution 1Recommended

Update VBA Worksheet Protection Arguments

Modify your macro's ActiveSheet.Protect statement to explicitly allow sorting and filtering when the worksheet is reprotected.

When a macro reprotects a worksheet, it resets user permissions to default restrictions. By appending the AllowSorting and AllowFiltering arguments to your VBA protection code, you grant users these specific permissions even while the sheet remains locked to structural changes.

Open the Visual Basic Editor

Go to the Developer tab on your Excel ribbon and click on 'Visual Basic', or press ALT + F11 to open the macro editor.

Locate the Reprotect Statement

Find the line of code in your macro that reprotects the worksheet after adding rows. It usually looks like 'ActiveSheet.Protect Password:="yourpassword"'.

Add Sorting and Filtering Arguments

Modify the protection statement by appending the required parameters. Change it to: ActiveSheet.Protect Password:="123", AllowSorting:=True, AllowFiltering:=True

Declare Macro Variables

Ensure all variables like 'x', 'y', and 'InsertRows' are properly declared at the beginning of your script (e.g., Dim InsertRows As Integer) and initialized to prevent runtime errors.

Save and Test

Save your VBA project, close the editor, and run your macro to verify that rows can be inserted and the table's sorting and filtering dropdowns remain active.

Update VBA Worksheet Protection Arguments

Check Syntax: Make sure you include commas between your arguments and verify that your password string matches exactly what is needed for the sheet.

Advanced Spreadsheet Editor

Manage Protected Sheets and Macros Seamlessly with WPS Spreadsheets

WPS Office provides an intuitive Developer interface to manage macros, VBA scripts, and advanced worksheet protection settings. Experience full compatibility with Microsoft Excel files completely free.

1. Open File in WPS Spreadsheets: Launch WPS Office and open your .xlsm or .xlsx file.
2. Access Worksheet Protection: Navigate to the 'Review' tab and click 'Protect Sheet'. Here you can manually tick 'Sort' and 'Use AutoFilter' without writing code.
3. Open Developer Tools: If you prefer VBA, go to the 'Developer' tab and select 'Visual Basic Editor' to access your macro scripts.
4. Apply Script Updates: Paste your updated macro with AllowSorting:=True and save the workbook securely.

Full compatibility with Microsoft Excel macro-enabled files (.xlsm)Intuitive worksheet protection and permission checkboxesBuilt-in Developer tools for robust macro editing and executionLightweight application with high performance

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro crash when trying to add rows to a protected sheet?

If the worksheet is protected without explicitly allowing row insertion or without using the macro-only protection flag, the script gets blocked. You must unprotect the sheet at the start of your macro using ActiveSheet.Unprotect and reprotect it at the end.

Can I allow formatting cells along with sorting and filtering?

Yes. You can add AllowFormattingCells:=True to your ActiveSheet.Protect statement. This lets users change cell colors, fonts, and borders while the worksheet structural protection remains active.

Does using UserInterfaceOnly help with macro errors on protected sheets?

Yes. Adding UserInterfaceOnly:=True to your protection statement allows VBA macros to make changes to a protected sheet without needing to unprotect and reprotect it, while still locking the sheet from manual user edits.

Excel & Data Problems