Expand an Excel Table on a Protected Worksheet with VBA
Question details
Expand an Excel table on a protected worksheet when a user selects the row below it.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Users need to add data to an Excel table while the worksheet remains protected.
- Observed behavior
- Worksheet protection can prevent the table from expanding automatically when users enter data below it.
In desktop Excel, identify the table name under Table Design and confirm the worksheet protection password. Unprotect the sheet, turn off the table's Total Row, and ensure the row below the table is empty and selectable.
Add a table row when the user selects the row below it
Use a worksheet selection event to temporarily remove protection, add one table row, and protect the sheet again.
This example adds a row when a user selects a single cell immediately below the table, before they enter data. It assumes the table's Total Row is turned off.
The example reapplies protection with default options. If the sheet needs specific protection permissions, add the appropriate arguments to the Protect statement.
Go to Review > Unprotect Sheet. Select the table's input cells and the blank row immediately below them, open Format Cells > Protection, and clear Locked so users can select and edit the new input row.
Press Alt+F11 to open the VBA editor. In Project Explorer, double-click the worksheet that contains the table.
Paste this code into the worksheet's code window. Replace Table1 with the name shown on Table Design and replace your_password with the sheet password: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim tbl As ListObject Dim nextRow As Range If Not Me.ProtectContents Then Exit Sub If Target.CountLarge <> 1 Then Exit Sub Set tbl = Me.ListObjects("Table1") Set nextRow = Me.Cells(tbl.Range.Row + tbl.Range.Rows.Count, tbl.Range.Column).Resize(1, tbl.Range.Columns.Count) If Intersect(Target, nextRow) Is Nothing Then Exit Sub On Error GoTo Failed Me.Unprotect Password:="your_password" tbl.ListRows.Add Me.Protect Password:="your_password", UserInterfaceOnly:=True Exit Sub Failed: MsgBox "Could not add the table row: " & Err.Description, vbExclamation On Error Resume Next Me.Protect Password:="your_password", UserInterfaceOnly:=True End Sub
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), enable macros when prompted, and use Review > Protect Sheet. Select a cell directly below the table and confirm that a new row appears and its intended input cells are editable.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Office formats, making it easy to move everyday spreadsheet work. The Excel VBA event solution still requires a compatible Excel environment.

Frequently Asked Questions
Why doesn't the table expand on a protected sheet?
Worksheet protection can prevent Excel from changing the table's structure. The VBA event temporarily unprotects the sheet, uses ListRows.Add, and then protects it again.
Where do I find my Excel table name?
Click any cell in the table, open the Table Design tab, and look for the Table Name box. Use that name in the ListObjects reference in the VBA code.
Why can't users type in the new table row?
The input cells may be locked. Unprotect the sheet, select the input cells and blank row below the table, and clear Locked under Format Cells > Protection before protecting the sheet again.
Will the macro run when I type directly below the table?
The example runs when you select a single cell immediately below the table, before typing. It requires macros to be enabled and the worksheet to be protected.
Does UserInterfaceOnly remain enabled after reopening the workbook?
No. Excel does not retain UserInterfaceOnly protection after reopening. The example sets it again when it adds a row; use a Workbook_Open procedure if your workbook needs that setting earlier.




