How to Update a Source Table from an Excel Dropdown Entry using VBA
Question details
The user needs to automatically update a record in a source table whenever a new value is entered next to an Excel dropdown.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating data entry where selecting or entering a value next to a dropdown dynamically updates the original dataset or table.
- Observed behavior
- A VBA Worksheet_Change event triggers when specific cells are modified, finds the corresponding record in the source table, and updates it without causing infinite recursive loops.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have backed up your data before running VBA scripts that overwrite source table values.
Use a Worksheet_Change VBA Event
Implement a VBA macro that detects changes in your dropdown cells and writes the new value back to the matching record in the source table.
This solution utilizes the Worksheet_Change event to monitor a specific range of cells. When a change occurs in this designated area, the code searches for the corresponding item in your source data sheet and updates the adjacent value.
It is crucial to temporarily disable application events during the execution of this script to prevent an infinite loop, where the macro changing the table triggers itself repeatedly.
Press Alt + F11 in Excel or WPS Spreadsheet to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer on the left, double-click the name of the worksheet where your dropdowns are located (e.g., Sheet1).
Copy and paste the Worksheet_Change code provided in the solution into the module window. You must adjust the target ranges (like "C4:C7") and the source sheet name (like "Ladder") to match the structure of your workbook.
Save your workbook. Change a value in your designated dropdown range and verify that the source table updates accordingly.
Automate Data with VBA in WPS Spreadsheet
WPS Office fully supports VBA (Visual Basic for Applications) macros, allowing you to run powerful automation scripts like Worksheet_Change events seamlessly. Create, edit, and execute your macros in a familiar, lightweight interface.
- 1. Download and Install: Get WPS Office for free from the official website and open your Excel workbook.
- 2. Enable Developer Tools: Go to the 'Developer' tab in the WPS Spreadsheet ribbon to access all macro and VBA features.
- 3. Open VBA Editor: Click on 'Visual Basic' or press Alt+F11 to enter your Worksheet_Change event code.
- 4. Run Your Macro: Modify your dropdown cells and let WPS Spreadsheet update your source tables instantly.
Frequently Asked Questions
Why doesn't my Worksheet_Change macro trigger when I change a cell?
Macros might be disabled in your trust center settings. Alternatively, 'Application.EnableEvents' may have been turned off by a previously interrupted script and not turned back on. You can reset it by typing 'Application.EnableEvents = True' in the Immediate Window (Ctrl+G) and pressing Enter.
How do I adjust the target range in the provided VBA code?
In the code line 'Intersect(Target, Range("C4:C7"))', change "C4:C7" to the specific cell or range where your dropdowns are located. The macro will only run if edits are made inside this specific range.
Can I use this macro to update a source table on a completely different worksheet?
Yes, the code is explicitly designed for this. The line 'Set fRng = Sheets("Ladder").Range("A14:A101")' directs the search to the "Ladder" worksheet. Simply update the sheet name inside the quotes to match your actual source worksheet.
What does Target.Offset(0, -2) mean in the VBA code?
The 'Offset(rowOffset, columnOffset)' function navigates relative to a specific cell. 'Target.Offset(0, -2)' refers to the cell located in the exact same row but two columns to the left of the changed cell. The macro uses this specific cell's value as the lookup parameter to find the matching record in the source table.
