VBA & Macro Problems

How to Fix an Excel VBA Due-Date Pop-Up Macro After Inserting Columns

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to fix an Excel VBA macro that generates a due-date pop-up on startup, which stopped working after inserting new columns into the worksheet.

Product: Excel
Device & OS: not provided
Scenario: Missing columns were inserted into a worksheet, causing existing column positions to shift and breaking the VBA macro's hardcoded cell references.
Observed behavior: The macro runs automatically when the workbook opens but incorrectly reports that there are no upcoming due dates, despite valid dates being present in the sheet.

Before you start

Before editing your VBA code, identify and note down the new column letters where your customer names and due dates are currently located.

Solution 1Recommended

Update the Hardcoded Column References in the VBA Editor

Manually update the range and column indexes within the macro's code to match the newly shifted layout of your worksheet.

When you insert or delete columns in a worksheet, standard cell formulas adjust automatically. However, hardcoded references in VBA macros (such as Range("C:C")) do not shift dynamically. You must manually correct these references in the code so the macro pulls data from the right place.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor.

Locate the startup macro

In the Project Explorer pane on the left, double-click on 'ThisWorkbook'. Macros designed to run on startup are typically found here under the 'Workbook_Open' subroutine.

Update the column references

Scan the code for references to the old column letters or numbers (e.g., Range("D:D") or Cells(i, 4)). Change these values to correspond with the new columns where the due dates now reside.

Save and test the workbook

Click the Save icon, close the VBA Editor, and save your file as an Excel Macro-Enabled Workbook (.xlsm). Close and reopen the file to verify the due-date pop-up triggers correctly with the sample dates.

Dynamic References Tip: To prevent this issue in the future, consider using Excel 'Named Ranges' instead of hardcoded column letters in your VBA code. Named Ranges automatically track shifted columns.

Manage Macros Easily with WPS

Use WPS Spreadsheet to Edit and Run VBA Macros

WPS Office provides robust support for VBA macros, allowing you to easily edit scripts, adjust shifted cell references, and automate workflows with excellent compatibility to Microsoft Excel.

1. Open your macro workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the due-date macro.
2. Access the Developer tab: Navigate to the 'Developer' tab on the top ribbon menu. If it's not visible, enable it from the settings.
3. Open the VBA Editor: Click on 'Visual Basic' to bring up the VBA Editor environment and locate your startup macro.
4. Modify and run: Update the affected column ranges in the script, save your changes, and reopen the file to see the pop-up function perfectly.

Seamless compatibility with Microsoft Excel macro-enabled workbooks (.xlsm)Built-in VBA editor for inspecting and updating shifted column referencesLightweight architecture ensures fast load times for complex macro filesFree and intuitive interface for everyday spreadsheet management

microsoft office alternative - wps office

Frequently Asked Questions

Why didn't my VBA macro update automatically when I inserted columns?

Unlike standard Excel formulas that dynamically adjust when you add or remove rows and columns, VBA macros usually contain static text strings that define cell ranges (e.g., Range("A1:A10")). These static references remain unchanged, leading the macro to read data from the wrong location after the layout changes.

How can I find out exactly which macro is running when the workbook opens?

Macros that execute upon opening the file are almost always located in the 'ThisWorkbook' module inside the VBA Editor. Press Alt + F11, double-click 'ThisWorkbook' in the Project Explorer, and look for a subroutine named 'Private Sub Workbook_Open()'.

Can I prevent my macros from breaking when users insert new columns?

Yes. The best practice is to assign 'Named Ranges' to specific headers or columns in your worksheet. You can then reference these names in your VBA code (e.g., Range("DueDateColumn")). If a user inserts a column, the Named Range updates automatically, and your VBA code will continue to point to the correct data.

Excel & Data Problems