VBA & Macro Problems

How to Insert or Delete Rows in a Protected Excel Table with VBA

WPS Content Manager Sep 28, 2026 869 views

Question details

The user needs to troubleshoot a VBA code that fails to insert or delete rows within an Excel table (ListObject) on a protected worksheet, despite working correctly on a different protected sheet.

How to Insert or Delete Rows in a Protected Excel Table with VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Running a VBA macro or UserForm to modify the row structure of a formatted table on a protected worksheet.
Observed behavior: The VBA macro successfully modifies rows on one protected sheet but triggers an execution error when attempting to insert or delete rows in a protected table on another sheet.

Before you start

Before modifying your VBA code, ensure you have the password to unprotect the worksheet and create a backup of your workbook to prevent accidental data loss during debugging.

Solution 1Recommended

Enable UserInterfaceOnly Protection in VBA

Applying protection with the UserInterfaceOnly property allows VBA macros to modify the sheet and its tables while keeping the user interface locked for manual edits.

By default, protecting an Excel sheet locks it against both user actions and VBA macro execution. Using the UserInterfaceOnly property bypasses the VBA restriction.

Note that this setting does not persist when the workbook is closed. It must be reapplied every time the workbook is opened, typically in the Workbook_Open event.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications Editor.

Locate the Workbook_Open Event

In the Project Explorer panel, double-click on 'ThisWorkbook'. Select 'Workbook' from the left dropdown and 'Open' from the right dropdown.

Add Protection Code

Enter the following code: Sheets("YourSheetName").Protect Password:="YourPassword", UserInterfaceOnly:=True.

Run and Test

Run the code or restart the workbook to apply the protection, then test your row insertion or deletion macro on the protected table.

Enable UserInterfaceOnly Protection in VBA

Table Specifics: When dealing with ListObjects (Tables), standard sheet protection might still restrict row insertion even with UserInterfaceOnly enabled. If it fails, you may need to temporarily unprotect the sheet.

Solution 2

Temporarily Unprotect and Reprotect the Sheet within the Macro

If UserInterfaceOnly fails for specific formatted table objects, you can explicitly unprotect the sheet before modifying the table and reprotect it immediately after.

Solution 3

Debug via a Sanitized Workbook and Forums

Complex VBA interacting with UserForms and Table protection may require external code review if standard protection bypasses fail.

Write and Run VBA Macros Easily with WPS Office

WPS Office provides excellent compatibility with Microsoft Excel's VBA macros. You can easily manage worksheet protection, run UserForms, and execute complex VBA scripts for table modifications in a highly optimized environment.

1. Open Your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
2. Access Developer Tools: Navigate to the 'Developer' tab located on the top ribbon.
3. Open VBA Editor: Click the 'Visual Basic' icon or press ALT+F11 to open the VBA Editor.
4. Edit and Execute: Modify your sheet protection settings or unprotect routines directly in the module, then seamlessly run your macro.

Fully compatible with Microsoft Excel (.xlsm, .xlsx) files and VBA scripts.Built-in Developer Tools to easily debug, write, and modify macros.Free and lightweight alternative with a familiar spreadsheet interface that eliminates the learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does VBA throw an error when inserting rows in a protected table?

By default, protecting a worksheet locks the structure of tables (ListObjects). Even if you allow row insertion in the protection dialogue, VBA might still be blocked unless the macro temporarily unprotects the sheet or is initialized with the UserInterfaceOnly:=True parameter.

What does UserInterfaceOnly:=True do in VBA?

This parameter, applied via VBA when protecting a sheet, locks the worksheet from manual user edits but allows VBA macros to make changes without needing to unprotect and reprotect the sheet continuously.

Can I allow users to insert rows manually while keeping the rest of the sheet protected?

Yes. When protecting the sheet via the Review tab > Protect Sheet, you can check the boxes for 'Insert rows' and 'Delete rows'. However, this behavior can sometimes be inconsistent when dealing with formatted Excel Tables (ListObjects), which may still require VBA workarounds for structural changes.

Excel & Data Problems