VBA & Macro Problems

How to Create an Excel VBA Change Log for Date, Time, and User ID

Partner Editor Oct 7, 2026 872 views

Question details

The user wants to automatically record the date, time, and user ID whenever a cell is modified on a specific Excel worksheet, saving the history to a separate log sheet.

How to Create an Excel VBA Change Log for Date, Time, and User ID

Product: Excel
Device & OS: not provided
Scenario: Tracking cell modifications to keep an audit trail of who changed what and when in a multi-tab workbook.
Observed behavior: The user needs a VBA macro that detects cell changes and writes the timestamp and username to a dedicated 'Log' worksheet within the same workbook.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your spreadsheet security settings.

Solution 1Recommended

Use the Worksheet_Change VBA Event to Create a Log

Implement a VBA script that automatically captures the current date, time, and Windows username whenever a cell on the monitored sheet is edited.

This method requires creating a dedicated worksheet named 'Log' in the same workbook. The VBA code must be pasted into the specific sheet module you want to monitor, not in a standard module.

Create the Log sheet

Insert a new worksheet and name it exactly 'Log'. In cell A1, type 'Date and Time', and in cell B1, type 'User ID'. Format column A as Date and Time.

Access the VBA Editor

Right-click the worksheet tab you want to monitor (the data sheet, not the Log tab) and select 'View Code' to open the Visual Basic Editor.

Insert the VBA code

Paste the following code into the worksheet module window: Private Sub Worksheet_Change(ByVal Target As Range) Dim logSheet As Worksheet Dim nextRow As Range Set logSheet = Worksheets("Log") Set nextRow = logSheet.Range("A" & logSheet.Rows.Count).End(xlUp).Offset(1) nextRow.Value = Now nextRow.Offset(0, 1).Value = Environ("username") End Sub

Save as a Macro-Enabled Workbook

Click 'File' > 'Save As', choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown, and save your file. Remember to enable macros when reopening the file.

Use the Worksheet_Change VBA Event to Create a Log

Workbook Placement: The 'Log' sheet must be located in the exact same workbook as the monitored data sheet for this code to function properly.

Automate with WPS Office

Create and Run VBA Macros Easily in WPS Spreadsheet

WPS Office provides robust support for Excel VBA macros, allowing you to automate tasks and track changes effortlessly using a familiar interface.

1. Open your workbook: Launch WPS Office and open your .xlsm data tracking workbook.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon and click 'Visual Basic' to open the built-in VBA Editor.
3. Apply and Run the Macro: Paste your Worksheet_Change macro code into the relevant sheet, save your progress, and let WPS Spreadsheet seamlessly log your data.

Fully compatible with Microsoft Excel VBA scripts and .xlsm formatsEasily automate data tracking and change logs with powerful VBA supportLightweight, fast performance, and highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA change log not recording any entries?

Ensure that macros are enabled in your workbook settings. Also, verify that the 'Log' sheet is located in the exact same workbook and the sheet name is spelled exactly as 'Log' without any extra spaces.

Can I track changes across all worksheets instead of just one?

Yes. Instead of using the Worksheet_Change event on a specific sheet, you can open the 'ThisWorkbook' module and use the Workbook_SheetChange event to monitor modifications across the entire workbook.

How do I format the Date and Time column correctly?

Select Column A in your Log sheet, right-click, choose 'Format Cells', navigate to the 'Number' tab, select 'Custom', and enter a format like 'mm/dd/yyyy hh:mm:ss' to display both date and time accurately.

Excel & Data Problems