VBA & Macro Problems

How to Record Worksheet Update Date, Time, and User ID in Excel using VBA

Adam Davis Sep 27, 2026 869 views

Question details

The user wants to configure an Excel workbook to automatically open on a specific Index sheet and use VBA to log the date, time, and user ID whenever data is entered into a specific column.

Product: Excel
Device & OS: not provided
Scenario: Automating workbook navigation upon opening and tracking user modifications for auditing purposes.
Observed behavior: Setting up automated VBA scripts for the Workbook_Open event and Worksheet_Change event to achieve automated tracking.

Before you start

Ensure you have enabled the Developer tab in your ribbon and have saved your file as an Excel Macro-Enabled Workbook (.xlsm) to allow VBA code to run properly.

Solution 1

Set the Workbook to Open on an Index Sheet

Use the Workbook_Open event to ensure the file always displays a specific sheet (like an Index or Home sheet) when opened.

By adding a simple macro to the ThisWorkbook module, you can force Excel to activate a specific worksheet every time the file is launched, which is highly useful for dashboards or index directories.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Access ThisWorkbook

In the Project Explorer pane on the left side, locate your current workbook and double-click on 'ThisWorkbook'.

Insert the Workbook_Open Code

In the code window, paste the following code: Private Sub Workbook_Open() Sheets("Index").Activate End Sub

Save the Workbook

Close the VBA editor and save your file. The next time you open the workbook, it will automatically switch to the sheet named 'Index'.

Sheet Name Dependency: Ensure you actually have a worksheet named exactly 'Index'. If the sheet name differs, replace 'Index' in the code with your actual sheet name.

Solution 2Recommended

Automatically Record Date, Time, and User ID on Data Entry

Use the Worksheet_Change event to detect edits in a specific column and automatically stamp the modification details in adjacent columns.

Advanced Macro Support

Use WPS Spreadsheet to Easily Run VBA and Macros

WPS Office Spreadsheet provides comprehensive support for VBA macros, allowing you to seamlessly execute automation scripts like update timestamps and user ID tracking. It fully supports .xlsm files and offers an intuitive built-in VBA editor.

1. Download and Install: Download WPS Office from the official website and install it on your device.
2. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your .xlsm file containing the change tracking macros.
3. Enable Macros: If prompted by a security warning at the top of the screen, click 'Enable Macros' to allow the VBA scripts to run.
4. Test the Automation: Type a value into Column A to instantly see the date, time, and your User ID populate in the adjacent columns.

Fully compatible with Microsoft Excel VBA code and macro formatsFree and lightweight office suite for WindowsBuilt-in Developer tools to write, edit, and debug macros quicklyFamiliar tabbed interface for a seamless transition

Frequently Asked Questions

How do I get the current Windows user ID in Excel VBA?

You can retrieve the current logged-in Windows user by using the Environ function in VBA. Simply type Environ("username") in your macro code to capture the User ID.

Why is my VBA code not running when I modify cells?

Ensure that your code is placed in the correct module. For modifying cells, the code must be placed in the specific Worksheet object (e.g., Sheet1) using the Worksheet_Change event, not in a standard Module. Additionally, check that Application.EnableEvents is set to True.

Can I track changes in specific columns only?

Yes. Inside the Worksheet_Change event, you can specify conditions like 'If Target.Column = 1 Then' to restrict the macro to run exclusively when cells in Column A are modified.

Will these macros work if multiple cells are updated at once?

If a user pastes data into multiple cells simultaneously, the basic Target setup might throw an error. You can handle this by adding 'If Target.Cells.Count > 1 Then Exit Sub' at the beginning of your script, or by looping through each cell in the Target range.

Excel & Data Problems