How to Make Manual Excel Input Replace a Dropdown Value with VBA
Question details
The user wants to configure an Excel VBA macro so that selecting a dropdown item adds or removes it from a cell's list, but manually typing text replaces the cell's entire value.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Customizing a Worksheet_Change event to differentiate between a data validation dropdown selection and manual keyboard input.
- Observed behavior
- The default Worksheet_Change event cannot distinguish the input method, causing the append or remove logic to trigger incorrectly when the user attempts to manually overwrite the cell with new text.
Ensure the Developer tab is enabled in your spreadsheet application and verify that your workbook is saved as a Macro-Enabled Workbook (.xlsm) before modifying event handlers.
Use Previous Value State to Route Dropdown vs. Manual Input
Since VBA cannot natively detect if input came from a dropdown list or the keyboard, you must capture the cell's prior state using the SelectionChange event to compare and route your logic correctly.
To achieve a cell that appends values from a dropdown but resets on manual input, you need two separate worksheet events. The Worksheet_SelectionChange event will temporarily store the cell's current value before editing begins. The Worksheet_Change event will then compare the new value against the list and the old value to decide whether to append, remove, or completely replace the text.
It is critical to temporarily disable application events while your VBA script updates the cell value to prevent the Worksheet_Change event from calling itself and causing an infinite loop.
Open the VBA Editor, double-click the specific worksheet module, and declare a variable at the very top (e.g., 'Dim PrevVal As String') to store the cell's prior state.
Create a 'Private Sub Worksheet_SelectionChange(ByVal Target As Range)' event. Inside, add logic to assign the target cell's current value to your 'PrevVal' variable before the user makes any changes.
Create the 'Private Sub Worksheet_Change(ByVal Target As Range)' event. First, check if the changed cell is your designated dropdown cell using 'If Not Intersect(Target, Range("A1")) Is Nothing Then'.
Add 'Application.EnableEvents = False'. Next, write an If/Else statement: if the new input matches a specific manual pattern (or isn't found in your dropdown source), assign 'Target.Value = Target.Value' (overwriting). Otherwise, use string manipulation to concatenate the new dropdown selection with 'PrevVal'.
Re-enable events with 'Application.EnableEvents = True' immediately after your cell update. Ensure you also add an 'On Error GoTo' block that restores events if the script crashes.

Handle VBA Dropdown Logic Seamlessly with WPS Office
WPS Spreadsheet fully supports VBA (Visual Basic for Applications) macros, allowing you to implement advanced Worksheet_Change events and custom data validation logic just like Microsoft Excel, with a highly optimized and familiar interface.
- 1. Access the Developer Tools: Open WPS Spreadsheet, navigate to the 'Developer' tab on the main ribbon, and ensure macros are enabled.
- 2. Open the VB Editor: Click on the 'VB Editor' button to launch the integrated VBA programming environment.
- 3. Select the Worksheet Module: In the Project Explorer pane on the left, double-click the specific worksheet where your dropdown list is located.
- 4. Insert Your VBA Code: Paste your custom Worksheet_SelectionChange and Worksheet_Change VBA code directly into the module window.
- 5. Save as Macro-Enabled: Click 'Save As' and select the 'Excel Macro-Enabled Workbook (*.xlsm)' format to preserve your VBA scripts.

Frequently Asked Questions
Why does my Worksheet_Change event freeze Excel?
This happens if your macro updates a cell's value without turning off events first. The update triggers the Worksheet_Change event again, creating an infinite loop. Always use Application.EnableEvents = False before modifying a cell, and set it back to True afterwards.
Can VBA directly detect if a value was typed or selected from a dropdown?
No, VBA does not have a native property to determine if input came from the keyboard or a Data Validation list. The most reliable workaround is to use the Worksheet_SelectionChange event to record the cell's value before editing, and compare it during the Worksheet_Change event.
How do I allow multiple selections in an Excel dropdown using VBA?
You can use the Worksheet_Change event to append the new dropdown selection to the existing cell value (often separated by a comma or line break) instead of replacing it. You must store the previous value using SelectionChange to concatenate them properly.
Does WPS Office support Excel VBA macros for data validation?
Yes, WPS Office provides comprehensive support for VBA macros. You can open .xlsm files, use the built-in VB Editor to write custom scripts, and automate data validation tasks with full compatibility with standard Excel VBA syntax.




