VBA & Macro Problems

How to Make Manual Excel Input Replace a Dropdown Value with VBA

Aamir Naveed AkramAamir Naveed Akram Nov 10, 2026 869 views

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.

Make Manual Excel Input Replace a Dropdown Value Using VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Declare a Global Variable

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.

2
Capture the Previous Value

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.

3
Intercept the Cell Change

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'.

4
Disable Events and Apply Logic

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'.

5
Restore Events and Error Handling

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.

Use Previous Value State to Route Dropdown vs. Manual Input
Prevent Infinite Loops: Always place Application.EnableEvents = True in your error-handling section so that Excel continues to register cell changes even if your macro encounters a bug.
Advanced Macros in WPS Spreadsheet

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. 1. Access the Developer Tools: Open WPS Spreadsheet, navigate to the 'Developer' tab on the main ribbon, and ensure macros are enabled.
  2. 2. Open the VB Editor: Click on the 'VB Editor' button to launch the integrated VBA programming environment.
  3. 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. 4. Insert Your VBA Code: Paste your custom Worksheet_SelectionChange and Worksheet_Change VBA code directly into the module window.
  5. 5. Save as Macro-Enabled: Click 'Save As' and select the 'Excel Macro-Enabled Workbook (*.xlsm)' format to preserve your VBA scripts.
Fully compatible with Microsoft Excel .xlsm and .xlsb formats.Built-in robust VBA Editor for writing, debugging, and managing macros.Reliable handling of complex Worksheet events and data validation dropdowns.Lightweight, fast performance, and free to download.
microsoft office alternative - wps office

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.