VBA & Macro Problems

How to Insert and Edit a New Row Below Active Cell Using Excel VBA

Jason Miller Oct 13, 2026 870 views

Question details

The user needs to automate a process using VBA to insert a copied row below the active cell, unlock specific columns (A through D) for editing, and automatically lock them again after the user finishes.

How to Insert and Edit a New Row Below Active Cell Using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Automating spreadsheet data entry where new rows are dynamically added and specific cells temporarily allow user edits before being securely protected.
Observed behavior: The user requires a structured VBA workflow to unprotect the sheet, manipulate row insertion, adjust the lock status of target cells, and re-protect the sheet seamlessly.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that you have saved a backup of your workbook as a Macro-Enabled Workbook (.xlsm) before testing new VBA code.

Solution 1Recommended

Create a VBA Macro to Insert, Unlock, and Protect Rows

Use this structured VBA approach to automatically insert a new row, allow edits in specific columns, and re-lock them upon completion.

To achieve this, you need to combine standard macro execution with a Worksheet_Change event. The standard macro handles the row insertion and unlocks the initial cells, while the event macro detects when the user finishes editing and locks the cells again.

Unprotect the Worksheet

At the beginning of your macro, insert the code `ActiveSheet.Unprotect Password:="yourpassword"` to allow structural changes to the worksheet.

Insert and Copy the Row

Write the code to copy the active row and insert it directly below using `ActiveCell.EntireRow.Copy` followed by `ActiveCell.Offset(1).EntireRow.Insert`.

Unlock Specific Columns for Editing

Target columns A through D in the newly created row and unlock them using the code `Range("A" & ActiveCell.Row + 1).Resize(1, 4).Locked = False`.

Re-protect with Formatting Allowed

Apply protection again so the user can only edit the unlocked cells by adding `ActiveSheet.Protect Password:="yourpassword", AllowFormattingRows:=True`.

Lock Cells After Completion

In the sheet's VBA module, add a `Worksheet_Change` event that monitors column D. When modified, use `ActiveSheet.Unprotect`, set the A-D range `.Locked = True`, move the selection to column E, and finally re-protect the sheet.

Create a VBA Macro to Insert, Unlock, and Protect Rows

Event Handler Placement: Remember that the Worksheet_Change event code must be placed in the specific Worksheet object module (e.g., Sheet1) in the VBA editor, not in a standard Module.

Run VBA Macros Seamlessly in WPS Spreadsheets

WPS Office provides robust support for VBA macros in its spreadsheet application, allowing you to run, edit, and create complex scripts to automate your workflow just like in Microsoft Excel.

1. Open WPS Spreadsheets: Launch WPS Spreadsheets and navigate to the Developer tab on the ribbon.
2. Access the VBA Editor: Click on 'Visual Basic' or 'Macros' to open the integrated VBA editor.
3. Insert Your Code: Paste your VBA script for inserting and protecting rows into a new module or the specific worksheet module.
4. Execute the Macro: Run the macro directly from the editor or assign it to a custom button in your spreadsheet for easy access.

Fully compatible with Microsoft Excel VBA scripts (.xlsm files)Lightweight application with fast macro execution speedsFamiliar developer interface for easy script managementComprehensive protection and cell formatting features

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a run-time error when trying to insert a row using VBA?

This usually happens if the worksheet is protected. You must include code to unprotect the sheet (`ActiveSheet.Unprotect`) before executing the row insertion command, and then re-protect it afterward.

How can I target specific columns in a newly inserted VBA row?

You can use the `Offset` and `Resize` properties. For example, `ActiveCell.Offset(1, 0).Resize(1, 4)` targets the first four columns of the row directly below the active cell.

Can I trigger a macro automatically when a user finishes typing in a cell?

Yes, you can use the `Worksheet_Change` event in your sheet's VBA module to detect when a specific cell or range (like column D) is modified, and execute subsequent code such as locking cells and moving to the next column.

How do I allow row formatting while a sheet is protected in VBA?

When applying the protect method in VBA, include the argument `AllowFormattingRows:=True`. This grants users permission to adjust row heights or formats even while the worksheet is protected against structural changes.

Excel & Data Problems