How to Record the Last Worksheet Change Date and Time Using VBA
Question details
The user needs to automatically record the exact date and time whenever a cell is modified on any worksheet, without having to write separate VBA event procedures for each individual sheet.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Tracking modifications across a workbook with dozens of sheets to keep an accurate log of when each worksheet was last updated.
- Observed behavior
- Instead of duplicating code across 50 individual sheet modules, the user wants a centralized script that leverages the Workbook_SheetChange event, including exceptions for specific overview sheets.
Ensure you save your spreadsheet as a Macro-Enabled Workbook (.xlsm) before adding the code, otherwise your VBA script will be permanently lost when you close the file.
Use the ThisWorkbook Module for Global Sheet Change Tracking
By placing a Workbook_SheetChange event in the ThisWorkbook module, you can track cell changes across all worksheets using a single block of VBA code.
Instead of maintaining separate Worksheet_Change codes for dozens of sheets, using the global Workbook_SheetChange event allows you to apply the timestamp rule to the entire file. You can easily use a Select Case statement to exclude specific summary or overview sheets from this automation.
Press the shortcut 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor window.
In the Project Explorer pane on the left side of the VBA editor, locate your current file and double-click on 'ThisWorkbook'.
Paste the following script into the blank code window: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Select Case Sh.Name Case "Overview1", "Overview2" Case Else Application.EnableEvents = False Sh.Range("Z1").Value = Now Application.EnableEvents = True End Select End Sub.
Modify the text 'Overview1', 'Overview2' in the code to match the exact names of the sheets you want to exclude. To change where the timestamp appears, change 'Z1' to your preferred cell reference.
Save your macro-enabled workbook. Go to any tracked worksheet and edit a cell. The current date and time will automatically appear in cell Z1 (or your specified cell).
Easily Manage VBA Macros in WPS Spreadsheet
WPS Office offers a fully integrated VBA environment, allowing you to seamlessly implement time-saving automations like the Workbook_SheetChange event to track worksheet updates.
- 1. Download and Install WPS Office: Get the latest version of WPS Office Free from the official website and open your macro-enabled spreadsheet.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon menu to access all macro and VBA functions.
- 3. Launch the VBA Editor: Click the 'Visual Basic' icon or press Alt+F11 to open the built-in VBA editor and implement your automated timestamp scripts.
Frequently Asked Questions
Why is my VBA timestamp not updating when I change a cell?
Macros might be disabled in your workbook. Check your security settings under the Developer tab to ensure macros are enabled. Also, if a previous macro error occurred while Application.EnableEvents was set to False, you must manually reset it to True in the Immediate Window for the event to fire again.
Do I need to update this code every time I add a new worksheet?
No. Because the code is placed in the global ThisWorkbook module, it automatically tracks changes on any newly added worksheets unless you explicitly add their names to the exclusion list in the Select Case statement.
How can I record the specific cell address that was modified?
The 'Target' parameter in the Workbook_SheetChange event refers to the specific cell that was changed. You can record 'Target.Address' in an adjacent cell to log exactly which cell triggered the timestamp.
Can I record the username of the person who made the change?
Yes, you can modify the VBA script to record 'Application.UserName' alongside the 'Now' function so you can track both the time of the change and the user who made it.
