Allow Manual Editing of Excel Multi-Select Drop-Down Values
Question details
The user wants to edit multi-select drop-down results and add values that are not in the source list without unwanted duplicates or restored entries.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses VBA to append multiple data-validation list selections to a cell.
- Observed behavior
- Manual edits can produce duplicates or bring back previous values instead of preserving the corrected text.
Open the workbook in desktop Excel and identify the cells that contain the data-validation lists. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and enable macros only if you trust the file.
Use separate add and edit modes in the worksheet macro
Use an edit-mode cell to distinguish manual corrections from selections that should be appended.
Excel's Worksheet_Change event does not reliably distinguish a typed value from a drop-down selection. This example uses B1 as a mode switch and D2:D100 as the multi-select range; change those references to suit your sheet.
The macro uses Application.Undo to retrieve the previous cell value, so normal undo history may be lost. It prevents duplicate additions but does not remove duplicates already present in a cell.
On the worksheet, enter Add in cell B1. Use Edit in B1 whenever you want to replace or correct a cell's full contents.
Select the drop-down cells, open Data > Data Validation, and on the Error Alert tab clear Show error alert after invalid data is entered. Keep the existing List validation and source. This allows typed values that are not in the list.
Right-click the worksheet tab and select View Code. If the sheet already has a Worksheet_Change procedure, merge the logic into that procedure rather than adding a second one.
In the worksheet code window, insert the following code. Adjust B1 and D2:D100 if needed. The comma-space delimiter assumes individual list values do not contain commas. Private Sub Worksheet_Change(ByVal Target As Range) Dim newValue As String, oldValue As String Dim item As Variant, result As String Dim alreadyPresent As Boolean If Target.CountLarge > 1 Then Exit Sub If Intersect(Target, Me.Range("D2:D100")) Is Nothing Then Exit Sub If LCase$(Trim$(CStr(Me.Range("B1").Value2))) = "edit" Then Exit Sub If IsError(Target.Value2) Then Exit Sub newValue = Trim$(CStr(Target.Value2)) If newValue = "" Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Application.Undo If IsError(Target.Value2) Then GoTo CleanUp oldValue = Trim$(CStr(Target.Value2)) result = newValue If oldValue <> "" Then For Each item In Split(oldValue, ",") If StrComp(Trim$(CStr(item)), newValue, vbTextCompare) = 0 Then alreadyPresent = True Exit For End If Next item If alreadyPresent Then result = oldValue Else result = oldValue & ", " & newValue End If End If Target.Value = result CleanUp: Application.EnableEvents = True End Sub
Save the workbook, return to the sheet, and select values with B1 set to Add. Set B1 to Edit, edit a cell directly to remove or change entries, or type a custom value. Set B1 back to Add to resume appending selections.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Excel format compatibility for moving everyday workbook tasks. It does not repair Excel VBA; macro support and compatibility can vary by WPS edition and platform.

Frequently Asked Questions
Why does a previous selection return after I edit the cell?
A multi-select Worksheet_Change macro may treat your edit as another selection and append it to the old text. Use a separate Edit mode to bypass the append logic while correcting the cell.
How can I enter a value that is not in the drop-down list?
Select the cells and open Data > Data Validation > Error Alert. Clear Show error alert after invalid data is entered, then use Edit mode to type the desired value.
Does the example remove duplicates already in a cell?
No. It prevents a repeated selection from being appended again. Set B1 to Edit and remove existing duplicates directly in the cell.
Can I use this VBA solution in Excel for the web?
Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel to edit and run the worksheet event code.




