VBA & Macro Problems

Fix a Microsoft Excel VBA Multi-Select Drop-Down That Cannot Delete Values

Chanuka GeekiyanageChanuka Geekiyanage Nov 12, 2026 870 views

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.

Fix a Microsoft Excel VBA Multi-Select Drop-Down That Cannot Delete Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the worksheet code module

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.

2
Set the target range and add the toggle macro

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

3
Save and test the drop-down

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.

4
Handle values that are not in the validation list

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.

Use a Worksheet_Change event that toggles selections with line-break separators
Keep event cleanup in place: The Application.EnableEvents = True line in the CleanUp section is essential. Without it, an error can leave Excel events disabled and prevent later Worksheet_Change macros from running.
Work with multi-select spreadsheet macros

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. 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. 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. 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. 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. 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.
Use data-validation drop-down lists for controlled multi-select entries.Store selections on separate lines so values containing commas remain intact.Open and edit Microsoft Excel .xlsx and macro-enabled .xlsm workbooks.Use a familiar spreadsheet interface for a smooth transition from Microsoft Excel.
microsoft office alternative - wps office

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.