Fix a Microsoft Excel VBA Multi-Select Drop-Down That Cannot Delete Values
Question details
The user needs an Excel VBA data-validation multi-select drop-down to add selected values and remove a value when it is selected again.

- Product
- Microsoft Excel VBA and Data Validation
- Device & OS
- not provided
- Scenario
- A worksheet uses a Worksheet_Change macro to combine multiple data-validation selections in one cell.
- Observed behavior
- Previously selected values cannot be removed, and values containing commas can be matched incorrectly when the macro uses commas as separators.
Confirm that the target cells use a Data Validation list and that every selectable item, including items such as "LATAM, Central America," exists in that list. Choose a separator that does not appear in list items; a line break is a practical choice when list values contain commas.
Use a Worksheet_Change event that toggles selections with line-break separators
Use this method when a second selection of the same drop-down item should remove it while preserving values that contain commas.
The macro captures the newly selected item, uses Undo to retrieve the cell's previous content, and then either adds or removes that item. It stores selections on separate lines instead of separating them with commas, so a list item that contains a comma remains one complete value.
In Excel, right-click the worksheet tab that contains the multi-select cells, select View Code, and paste the event procedure into that worksheet's code window—not into a standard module.
Replace B2:B100 with your validated-cell range, then use this code: Private Sub Worksheet_Change(ByVal Target As Range) Const TargetRange As String = "B2:B100" Dim picked As String, previousValue As String If Target.CountLarge > 1 Then Exit Sub If Intersect(Target, Me.Range(TargetRange)) Is Nothing Then Exit Sub If Target.Validation.Type <> xlValidateList Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False picked = Target.Value Application.Undo previousValue = Target.Value If Len(picked) = 0 Then Target.Value = "" Else Target.Value = ToggleMultiSelectItem(previousValue, picked) End If CleanUp: Application.EnableEvents = True End Sub Private Function ToggleMultiSelectItem(ByVal previousValue As String, ByVal picked As String) As String Dim items() As String, i As Long, result As String, found As Boolean If Len(previousValue) = 0 Then ToggleMultiSelectItem = picked Exit Function End If items = Split(previousValue, vbLf) For i = LBound(items) To UBound(items) If StrComp(items(i), picked, vbTextCompare) = 0 Then found = True ElseIf Len(result) = 0 Then result = items(i) Else result = result & vbLf & items(i) End If Next i If Not found Then If Len(result) = 0 Then result = picked Else result = result & vbLf & picked End If End If ToggleMultiSelectItem = result End Function
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm), enable macros when reopening it, then select an item from the Data Validation arrow. Select the same item again to remove it; select a different item to add it on a new line.
If an old cell value is not available in the current Data Validation list, clear the cell with Delete and reselect the values to retain from the drop-down. Alternatively, update the validation source list to include that value before using the macro.

Create and run a multi-select data-validation macro in WPS Spreadsheet
WPS Spreadsheet lets you manage data-validation lists and, in supported desktop installations, work with VBA-compatible macros for multi-select workflows. Add the worksheet event code, set the target range, and save the file as a macro-enabled workbook to use the same add-or-remove selection behavior.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose WPS Spreadsheet, and open the Excel workbook that contains the data-validation list.
- 2. Check the validation list range: Select a target cell, open Data Validation from the Data tab, and confirm that the list source includes every permitted value.
- 3. Open the macro editor: Use Tools > Macro > Visual Basic Editor, then open the code module for the worksheet that contains the multi-select cells.
- 4. Add the Worksheet_Change procedure: Paste the toggle macro into the worksheet module and update the TargetRange value so it matches your data-validation cells.
- 5. Save as a macro-enabled file and test: Use Save As to save the workbook as an .xlsm file, enable macros if prompted, and select an item twice to confirm that it is removed.

Frequently Asked Questions
Why does selecting an item a second time not remove it in my multi-select drop-down?
A standard Data Validation list replaces the cell value rather than tracking previous selections. The Worksheet_Change event must save the new selection, use Application.Undo to read the previous value, and explicitly remove the selected item when it already exists.
How can I use a multi-select list when one of the values contains a comma?
Do not use commas as the stored-value separator. Store each selected item with vbLf, which creates a line break, and compare complete Split(previousValue, vbLf) items instead of searching comma-separated text.
Why can I not remove an old value that is no longer in the drop-down list?
The macro can only toggle an item that is selected from the current validation list. Add the old value back to the validation source, or clear the cell and select the valid values you want to keep.
Where should I paste the Worksheet_Change code?
Paste it in the specific worksheet's code module: right-click that worksheet tab, select View Code, and insert the procedure there. It will not run correctly if placed only in a standard module.
What should I do if my other Excel macros stop running after an error?
Open the VBA editor and run Application.EnableEvents = True in the Immediate Window, then ensure the event procedure has an error-cleanup section that always restores Application.EnableEvents to True.




