How to Auto-Populate User Name in Column G When Column F Changes Using Excel VBA
Question details
The user needs to use Excel VBA to automatically record the current application user's name in column G whenever a cell's data in the adjacent column F is entered or modified.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a tracker or log in a spreadsheet where user edits in a specific column are automatically attributed to the person making the change.
- Observed behavior
- The user needs a reliable VBA script that updates the adjacent cell properly and does not break or stop working when rows and cells are deleted.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macros are enabled in your Trust Center settings so the VBA code can execute properly.
Implement a Worksheet_Change Event Macro
Use a VBA script triggered by the Worksheet_Change event to detect modifications in Column F and automatically write the username to Column G.
This solution utilizes Excel's built-in Worksheet_Change event, which monitors the worksheet for any data entry or edits. The script checks if the change occurred in the target column and safely updates the adjacent cell while handling background events to prevent the macro from crashing during row deletions.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.
In the Project Explorer pane on the left, find your workbook and double-click the specific worksheet name (e.g., Sheet1) where you want this tracking to occur.
Copy and paste the following code into the worksheet module window: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.CountLarge > 1 Then Exit Sub If Target.Column <> 6 Then Exit Sub Application.EnableEvents = False On Error GoTo CleanUp Cells(Target.Row, "G").Value = UCase$(Split(Application.UserName, ",")(0)) CleanUp: Application.EnableEvents = True End Sub
Close the VBA editor and return to your worksheet. Type any value into a cell in Column F and press Enter. The adjacent cell in Column G will automatically display your username.
Automate Data Tracking Seamlessly with WPS Spreadsheet
WPS Spreadsheet provides robust support for VBA macros. You can run the exact same Worksheet_Change event script in WPS Office to automate user name logging without needing to alter your code.
- 1. Open your Document: Launch WPS Spreadsheet and open the worksheet you want to track.
- 2. Access the VBA Editor: Navigate to the Tools tab and click on 'Macros', or press ALT + F11 to open the VBA editor.
- 3. Insert the Tracking Code: Double-click your target sheet in the left pane and paste the Worksheet_Change tracking code.
- 4. Save as Macro-Enabled: Save your file as a Macro-Enabled Workbook to keep the automation active.
Frequently Asked Questions
Why does my VBA code stop working after I delete rows or cells?
If your macro crashes or you delete rows while Application.EnableEvents is set to False, Excel will not re-enable events automatically, causing the macro to stop triggering. Using an error handler like 'On Error GoTo CleanUp' ensures that EnableEvents is always restored to True before exiting.
Can I have multiple Worksheet_Change events on the same sheet?
No, Excel only allows one Worksheet_Change event per worksheet. If you have multiple tasks to trigger upon cell changes, you must combine them into a single procedure using If statements or Select Case logic based on the Target.Column or Target.Address.
Where does the macro pull the user's name from?
The script uses Application.UserName, which pulls the name registered in your Office application settings. You can view or change this name by going to File > Options > General and looking under the 'Personalize your copy of Microsoft Office' section.
