VBA & Macro Problems

How to Automatically Set Status and Close Date Using Excel VBA

Muhammad TalhaMuhammad Talha Nov 3, 2026 873 views

Question details

The user wants to automate a status tracker so that entering a close date changes the status to 'Closed', and selecting 'Closed' auto-fills today's date.

How to Automatically Set Status and Close Date Using Excel VBA
Product
Excel
Device & OS
not provided
Scenario
Managing an issue tracker or task list in Excel where status and completion dates need to remain synchronized without manual double-entry.
Observed behavior
The goal is to automatically update corresponding cells in the same row when either the status column or the close date column is modified by utilizing VBA.
Before you start

Ensure your Excel workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macros are enabled in your Trust Center settings to allow the VBA code to run properly.

Solution 1Recommended

Use Worksheet_Change Event to Automate Status and Date Updates

Implement a VBA macro on the specific worksheet to monitor target columns and update related cells automatically.

This method uses the Worksheet_Change event to listen for edits in your status and date columns. To avoid infinite loops where the macro triggers itself, the code uses Application.EnableEvents to temporarily pause event tracking while updating the target cells.

1
Open the VBA Editor

Right-click the sheet tab at the bottom of Excel where your tracker is located and select 'View Code', or press ALT + F11 to open the Visual Basic for Applications window.

2
Add the Worksheet_Change Code

In the code window for your specific sheet, select 'Worksheet' from the left dropdown and 'Change' from the right. This will automatically generate the Private Sub Worksheet_Change(ByVal Target As Range) block.

3
Monitor Date and Status Columns

Inside the sub, write an 'If Target.Column = [YourDateColumnNumber]' statement. If a date is entered, use Target.Offset to update the status column to 'Closed'. Add an 'ElseIf Target.Column = [YourStatusColumnNumber]' to check if the status is set to 'Closed' and use Target.Offset to input 'Date' (today's date) in the date column.

4
Disable and Enable Events

Wrap your cell-value-changing code inside 'Application.EnableEvents = False' and 'Application.EnableEvents = True'. This is crucial to prevent the Worksheet_Change event from recursively calling itself and crashing Excel.

Use Worksheet_Change Event to Automate Status and Date Updates
Save as XLSM: Always remember to save your file via File > Save As and choose 'Excel Macro-Enabled Workbook (*.xlsm)'. Otherwise, your VBA code will be lost upon closing.
Try Advanced Macros in WPS Office

Use WPS Spreadsheet to Run and Edit VBA Macros

WPS Office provides excellent compatibility with Excel macros, allowing you to use Worksheet_Change events to automate your task trackers smoothly.

  1. 1. Open your XLSM file in WPS: Launch WPS Spreadsheet and open your macro-enabled tracking file.
  2. 2. Access the Developer Tools: Navigate to the 'Developer' tab in the top ribbon and click on 'Macros' or 'Visual Basic' to access the VBA editor.
  3. 3. Enable Macros: If prompted by the security warning below the ribbon, click 'Enable Macros' to allow the Worksheet_Change event to trigger.
  4. 4. Test the Automation: Type a date into your target column or enter 'Closed' in the status column to see the corresponding cells update automatically.
Fully compatible with Microsoft Excel .xlsm files and VBA scripts.Intuitive Visual Basic Editor to write, debug, and execute macros.Lightweight installation with fast processing for complex datasets.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel freeze or crash when the macro runs?

This usually happens because changing a cell within a Worksheet_Change event triggers the event again, causing an infinite loop. Always use Application.EnableEvents = False before changing cell values, and set it back to True immediately after.

Can I use this VBA code on multiple sheets at once?

Yes, instead of placing the code in a specific worksheet module, you can place it in the ThisWorkbook module under the Workbook_SheetChange event to apply it across the entire workbook.

How do I handle users clearing the date or status cells?

You should add an If IsEmpty(Target) or Target.Value = "" condition in your VBA code to either exit the sub or clear the corresponding adjacent cell to maintain accurate tracking.

Why is my macro not running when I type a date?

Make sure that your workbook is saved as an .xlsm file, macros are enabled in the Excel Trust Center, and the code is placed in the correct worksheet module rather than a standard module.