How to Record the Actual Excel Co-Author's Name When Data Changes
Question details
The user wants to permanently record the identity of the specific co-author who entered data into a cell, rather than displaying the name of the user currently viewing the file.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking who modifies specific cells in an Excel co-authoring file hosted on OneDrive.
- Observed behavior
- When using formula-based approaches like Application.UserName, the function recalculates when the file is opened, displaying the current active user's name instead of the person who originally made the data entry.
Ensure your file is saved as a Macro-Enabled Workbook (.xlsm) and that VBA macros are enabled in your Trust Center settings so the event code can run properly.
Use a VBA Worksheet_Change Event to Log Usernames
Use a VBA event to capture the user's local system name at the exact moment a cell is modified, permanently writing it as plain text to prevent it from updating when others open the file.
Formulas natively recalculate upon opening a workbook, making them unsuitable for permanently capturing the identity of a co-author. By utilizing the Worksheet_Change event in VBA, we can detect a change in a specific column and immediately output the active Windows username as a static string in an adjacent cell.
Open your target Excel workbook and press Alt + F11 to launch the Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left, double-click the specific worksheet where you want to monitor changes (for example, 'Sheet1').
Paste the following code into the code window. This example monitors cells C4 through C26 and logs the editor's username in column B of the corresponding row: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range Set rng = Intersect(Target, Range("C4:C26")) If rng Is Nothing Then Exit Sub Application.EnableEvents = False For Each cell In rng Cells(cell.Row, "B") = Environ("UserName") Next cell Application.EnableEvents = True End Sub
Close the VBA Editor. Save your document as an Excel Macro-Enabled Workbook (.xlsm). Type a value in column C (rows 4 to 26) and press Enter; you will see your Windows username automatically appear in column B.
Run VBA Macros Flawlessly with WPS Spreadsheet
WPS Office offers comprehensive support for VBA macros, enabling you to use Worksheet_Change events and automate your data tracking seamlessly. It's a lightweight, fully compatible, and cost-effective productivity suite.
- 1. Download and Install WPS Office: Get WPS Office from the official website and install it on your computer.
- 2. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm file to continue working with your existing data.
- 3. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click 'Visual Basic Editor' to manage your Worksheet_Change events effortlessly.
Frequently Asked Questions
Why does Application.UserName change when someone else opens the shared file?
When Application.UserName is used within a formula, Excel recalculates that formula every time the workbook is opened or refreshed. This causes the cell to always display the name of the user currently viewing the file, rather than the original author who entered the data.
Will Environ("UserName") work on Mac devices?
Environ("UserName") is primarily designed to fetch environment variables on Windows systems. If your co-authors are using Mac computers, this VBA command may fail or return an empty string. A Mac-specific script (such as using MacScript) is typically required to retrieve local Mac usernames.
Is there a way to track cell changes without using VBA macros?
Yes. If your file is stored in OneDrive or SharePoint, you can use the built-in 'Show Changes' feature in the Review tab. This provides a visual log of who changed what and when, though it won't write the author's name permanently into an adjacent spreadsheet cell like the VBA method does.
