VBA & Macro Problems

Allow Manual Editing of Excel Multi-Select Drop-Down Values

John WilsonJohn Wilson Nov 18, 2026 869 views

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.

Allow Manual Editing of Excel Multi-Select Drop-Down Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the mode 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.

2
Allow values outside the list

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.

3
Open the worksheet code

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.

4
Add the change handler

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

5
Test both modes

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.

Use separate add and edit modes in the worksheet macro
If an edit cannot be undone: Application.Undo may fail for some changes. If the macro cannot retrieve the previous value, review the cell and use Edit mode to enter the intended contents.
Free Microsoft Office alternative

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.

Open and edit common Microsoft Excel formats, including XLSX filesUse a familiar spreadsheet interface for everyday data entryWork with spreadsheet drop-down lists without changing applicationsMove routine spreadsheet work to a lightweight office suite
microsoft office alternative - wps office

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.