How to Create Permanent Excel Timestamps with VBA Events
Question details
The user needs to insert timestamps that do not automatically update or recalculate when the workbook is edited or reopened.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking data entry times or logging actions permanently without the timestamps changing dynamically over time.
- Observed behavior
- Standard worksheet functions like NOW() or TODAY() are volatile and recalculate constantly, overwriting the original timestamp.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer options and macros in your spreadsheet settings.
Use a Worksheet_Change VBA Event for Static Timestamps
Replace volatile formulas with a VBA macro that triggers automatically when a specific range of cells is modified, pasting the exact time as a static value.
Functions like NOW() recalculate every time the sheet calculates, meaning your original timestamp is lost. By using VBA's Worksheet_Change event, you can capture the exact moment a cell is edited and write it as a hardcoded value that will never change unless edited again.
Right-click the worksheet tab at the bottom of your screen where you want the timestamps to appear, and select 'View Code' from the context menu.
In the code window, paste the Worksheet_Change VBA code. For example, to track inputs in S2:S50 and place timestamps in T2:T50, use: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, cel As Range Set rng = Intersect(Range("S2:S50"), Target) If Not rng Is Nothing Then On Error GoTo ExitHandler Application.ScreenUpdating = False Application.EnableEvents = False For Each cel In rng If cel.Value = "" Then cel.Offset(0, 1).ClearContents Else cel.Offset(0, 1).Value = Now End If Next cel ExitHandler: Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
Close the VBA editor and return to your worksheet. Type a value into cell S2 and press Enter. A permanent timestamp will instantly appear in T2.
Automate Timestamps Using WPS Spreadsheet Macros
WPS Spreadsheet offers built-in VBA support, allowing you to run, edit, and create advanced macros like automated timestamps exactly as you would in Microsoft Excel.
- 1. Enable Developer Tools: Open your document in WPS Spreadsheet and navigate to the 'Developer' tab on the top ribbon.
- 2. Open the VBA Editor: Click on 'Visual Basic' or 'Macros' to launch the built-in VBA editor.
- 3. Apply the Code: Double-click the relevant sheet in the Project Explorer on the left, and paste your Worksheet_Change event code.
- 4. Save as Macro-Enabled: Save your file as a macro-enabled workbook (.xlsm) to ensure your automated timestamps work seamlessly every time you open the file.
Frequently Asked Questions
Why does the NOW() function keep changing my timestamps?
The NOW() and TODAY() functions are 'volatile', meaning they recalculate every time the worksheet calculates or is reopened. To keep a timestamp static, you must either enter it manually (using Ctrl+Shift+;) or use VBA to insert it as a hardcoded value.
What happens if I delete the input data in the VBA method?
In the provided VBA code, there is a condition that checks if the input cell is blank. If you clear the data in the input cell, the macro will automatically clear the contents of the corresponding timestamp cell to keep your data clean.
Do I need to manually run the macro every time?
No. By using the 'Private Sub Worksheet_Change' event, the macro listens in the background. It executes automatically without any manual intervention whenever a change is detected in your specified target range.
