How to Automatically Add New Vendors to an Excel VBA ComboBox
Question details
The user wants to allow typing a new vendor name into an Excel UserForm ComboBox, display an approval warning, append the new vendor to the source worksheet, and automatically resort the list.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Entering new, unlisted data into a UserForm ComboBox linked to a worksheet range.
- Observed behavior
- The user needs a VBA routine to dynamically handle new entries by prompting the user for approval, updating the worksheet list, sorting the updated list, and refreshing the ComboBox's RowSource without disrupting other form formatting rules.
Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have identified the exact worksheet name and column range where your vendor list is stored.
Use the BeforeUpdate Event to Append and Sort New Vendors
Utilize the BeforeUpdate event in your UserForm to validate the entry against the source list, prompt the user for confirmation, and dynamically update the worksheet.
When a user types a value not present in the ComboBox's linked RowSource, you can capture this action using the BeforeUpdate event. By verifying whether the text exists in the current list (using the MatchFound property), VBA can trigger a message box asking for approval.
If approved, the new entry is appended to the bottom of the source list. Finally, the script sorts the updated list and refreshes the ComboBox so the new vendor is immediately available.
Press ALT + F11 to open the VBA Editor, then double-click your UserForm in the Project Explorer window.
Right-click the specific ComboBox (e.g., ComboBox1) and select View Code to open the event procedure window.
Create a private sub for the `ComboBox1_BeforeUpdate` event. Add an IF statement to check if `ComboBox1.MatchFound` is False and if `ComboBox1.Value` is not empty.
Within the IF statement, use `MsgBox` to ask the user if they want to add the new vendor. If the response is vbYes, find the last empty row in your vendor worksheet (using `Cells(Rows.Count, 1).End(xlUp).Row + 1`) and set its value to `ComboBox1.Value`.
Apply the `Range.Sort` method to your vendor column to maintain alphabetical order. Finally, reload the ComboBox list by redefining its `RowSource` property to encompass the newly added row.
Create and Run VBA Macros Seamlessly in WPS Office
WPS Office fully supports VBA macros, allowing you to create UserForms, manage ComboBox events, and automate your data entry workflows just as you would in Microsoft Excel.
- 1. Download and Install: Download WPS Office and ensure you install the version that includes the Developer tools and VBA module support.
- 2. Open your Macro-Enabled Workbook: Launch WPS Spreadsheets and open your .xlsm file containing the UserForm.
- 3. Access the Developer Tools: Go to the Developer tab on the top ribbon and click on Macro Editor to view or edit your ComboBox VBA code.
- 4. Run your UserForm: Execute the UserForm directly within WPS Spreadsheets to test adding and sorting new vendors automatically.
Frequently Asked Questions
Why doesn't the ComboBox RowSource update automatically when a new vendor is added?
The RowSource property is static by default unless it is tied to a dynamic named range or updated via VBA. You must reset the RowSource property in your script after appending a new vendor so the ComboBox registers the new row.
What event should I use to detect a new entry in a ComboBox?
The `BeforeUpdate` or `Exit` event is ideal. It triggers after the user finishes typing a value and attempts to leave the ComboBox, allowing you to intercept and validate the entry before moving to the next field in your UserForm.
Can I prevent users from entering unlisted vendors entirely?
Yes. By setting the `MatchRequired` property of the ComboBox to True in the Properties window, users will be forced to select an existing item from the list, and any new custom entry will be rejected automatically.
