How to Automatically Keep the Previous Update Date in Excel with VBA
Question details
The user wants to use a VBA macro and a form control button to record the current date in one cell while automatically shifting the existing date to another cell to maintain a history of updates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking document update history and timestamps.
- Observed behavior
- Manually updating a date cell overwrites the previous value; the goal is to preserve the last update date automatically when logging a new one.
Ensure that the Developer tab is enabled in your Excel ribbon and that you have permission to run and save Macro-Enabled Workbooks (.xlsm).
Use a VBA Macro with a Form Control Button
Create a simple VBA script that shifts the current date to a 'previous' cell and inserts today's date into the 'current' cell, triggered by a clickable button.
This solution relies on a short VBA macro to check if a date already exists in the target cell. If it does, it moves that date to a secondary cell before updating the primary cell with the current date.
Select cells A1 and A2 (or your chosen cells), right-click, choose 'Format Cells', and set them to your preferred Date format.
Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor. Go to 'Insert' > 'Module' to create a new standard module.
Copy and paste the following code into the module window: Sub SetUpdated() If Range("A1").Value <> "" Then Range("A2").Value = Range("A1").Value End If Range("A1").Value = Date End Sub
Close the VBA editor. Go to the Developer tab on the ribbon, click 'Insert', and choose 'Button (Form Control)'. Click and drag on your worksheet to draw the button.
When the 'Assign Macro' dialog appears automatically, select 'SetUpdated' from the list and click 'OK'. You can right-click the button to edit its text (e.g., 'Update Date').
Go to 'File' > 'Save As' and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown to ensure your VBA code is saved permanently.
Track Update Dates Easily in WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros and Excel's .xlsm file formats, allowing you to implement customized date-tracking automation just like you would in Microsoft Excel.
- 1. Enable Developer Tools: Open your workbook in WPS Spreadsheet. Go to the 'Developer' tab on the top ribbon to access VBA and macro settings.
- 2. Write Your Macro: Click 'Visual Basic' or press Alt+F11, insert a new Module, and paste your update date tracking VBA code.
- 3. Insert a Control Button: Under the Developer tab, click 'Insert', select a Form Control Button, place it on the sheet, and assign your macro to it.
- 4. Save and Run: Save the file as a Macro-Enabled Workbook (.xlsm) and click your newly created button to update dates automatically.
Frequently Asked Questions
Why doesn't my date update macro run when I click the button?
Ensure that macros are enabled in your Trust Center or security settings. Also, verify that the workbook is saved correctly as a Macro-Enabled Workbook (.xlsm), as standard .xlsx files cannot store VBA code.
Can I use different cells instead of A1 and A2?
Yes. Simply change the 'A1' and 'A2' references in the VBA code (e.g., Range("B5").Value) to the specific cell references you want to use for logging the current and previous dates.
Will this macro overwrite existing data in the previous date cell?
Yes, each time you run the macro by clicking the button, the date currently in the first cell replaces whatever data is in the second (previous date) cell.
Is there a way to record the exact time along with the date?
Yes, you can modify the VBA code to use the 'Now' function instead of the 'Date' function (e.g., Range("A1").Value = Now). This captures both the current date and the system time.
