VBA & Macro Problems

Display a Message When Users Insert Rows or Columns in Excel

Anum MushtaqAnum Mushtaq Nov 22, 2026 868 views

Question details

The user wants Excel to remind users to update formulas and conditional formatting when they insert rows or columns.

Display a Message When Users Insert Rows or Columns in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Users need to unprotect a worksheet before making structural changes, then insert rows or columns and review affected formulas and formatting.
Observed behavior
The desired result is a reminder after an insertion so users can check formulas and conditional formatting.
Before you start

Make sure you can unprotect the worksheet and have permission to run macros. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) before adding VBA code.

Solution 1Recommended

Use Excel VBA Buttons to Insert Rows or Columns and Show a Reminder

Use this approach when users can insert rows and columns through designated macro buttons.

Excel does not provide a dedicated worksheet event that reliably fires specifically for every row or column insertion. These macros show a reminder after an insertion made through the assigned buttons; they do not detect insertions made through Excel's standard menus.

Add this code to a standard VBA module: Sub InsertRowWithReminder() If TypeName(Selection) <> "Range" Then Exit Sub If ActiveSheet.ProtectContents Then MsgBox "Unprotect the worksheet before inserting a row." Exit Sub End If Selection.EntireRow.Insert MsgBox "Row inserted. Check formulas and conditional formatting." End Sub Sub InsertColumnWithReminder() If TypeName(Selection) <> "Range" Then Exit Sub If ActiveSheet.ProtectContents Then MsgBox "Unprotect the worksheet before inserting a column." Exit Sub End If Selection.EntireColumn.Insert MsgBox "Column inserted. Check formulas and conditional formatting." End Sub

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

2
Add the macros

In the VBA editor, select the workbook in Project Explorer, choose Insert > Module, and paste the two macros into the module.

3
Create insertion buttons

Return to the worksheet and choose Developer > Insert > Button (Form Control). Draw a button and assign InsertRowWithReminder; repeat for InsertColumnWithReminder.

4
Test the reminder

Save the .xlsm workbook, enable macros if prompted, and use Review > Unprotect Sheet if necessary. Select one cell and click the row or column button; then check the reminder and the affected formulas and formatting.

Use Excel VBA Buttons to Insert Rows or Columns and Show a Reminder
Standard insert commands are not monitored: Users who insert rows or columns through the ribbon or right-click menu will not see these macro reminders. The macros also do not automatically extend formulas or conditional formatting.
Free Microsoft Office alternative

Review Excel Workbooks in WPS Spreadsheet

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel files to review formulas and conditional formatting as you move between office suites. Excel-specific VBA macros may require Microsoft Excel.

Review spreadsheet formulas and conditional formatting in a familiar interfaceOpen and edit compatible Microsoft Excel .xlsx workbooksUse a free, lightweight office suite for everyday spreadsheet workMove between common Office document formats with less disruption
microsoft office alternative - wps office

Frequently Asked Questions

Does Excel have an insert-row or insert-column VBA event?

Excel has no dedicated worksheet event that reliably identifies every row or column insertion. A macro button can insert the row or column and display a message, but it will not catch insertions made through standard commands.

Why does the macro say the worksheet is protected?

The example macros require an unprotected worksheet. Select Review > Unprotect Sheet and enter the password if prompted before using the button.

Will the reminder update formulas and conditional formatting automatically?

No. After inserting a row or column, inspect the affected formulas and use Home > Conditional Formatting > Manage Rules to review the rules and their applied ranges.

Does protecting the workbook structure prevent row insertion?

No. Workbook structure protection controls changes to sheets, such as adding or deleting them. Use Review > Protect Sheet and restrict Insert rows and Insert columns to control insertion within a worksheet.

Why do the buttons stop working after I reopen the workbook?

Save the file as an Excel Macro-Enabled Workbook (.xlsm) and enable macros when prompted, if you trust the workbook. Macro security settings may otherwise prevent the buttons from running.