VBA & Macro Problems

Fix Duplicate Text in Excel Multiple-Selection Drop-Down Macros

Chanuka GeekiyanageChanuka Geekiyanage Oct 29, 2026 869 views

Question details

The user needs to resolve errors where a custom VBA macro meant to enable multiple selections from a drop-down list is malfunctioning.

How to Fix Duplicate Text from an Excel Multiple-Selection Drop-Down Macro
Product
Excel
Device & OS
not provided
Scenario
Using a VBA macro script to allow users to select multiple items from a standard data validation drop-down list.
Observed behavior
The macro causes duplicate text to be inserted, interferes with standard cell-selection, and generates unusual worksheet outlines.
Before you start

Before modifying your VBA script, ensure you have saved a backup copy of your workbook to prevent accidental data loss during troubleshooting.

Solution 1

Disable the VBA Macro to Confirm the Cause

Isolate the issue by disabling macros to verify whether the duplicate text and cell-selection problems are caused by the VBA code or standard Excel data validation.

Since standard data validation alone cannot cause infinite loops or duplicate appending, disabling the macro will confirm that the script is the root cause of the unusual worksheet outlines and selection issues.

1
Open Trust Center Settings

Navigate to the Developer tab on the ribbon and click on 'Macro Security' to open the Trust Center.

2
Disable All Macros

Select the 'Disable all macros with notification' option and click 'OK'. Restart Excel and test the drop-down list to confirm standard validation works without duplicating text.

Manage Macros Easily

Seamlessly Run and Edit VBA Macros with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for VBA and macros, allowing you to run, edit, and troubleshoot custom drop-down scripts with an intuitive interface just like you would in Excel.

  1. 1. Open Your Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the multiple-selection drop-down list.
  2. 2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon and click on 'VBA Editor'.
  3. 3. Fix the Macro Code: Double-click the target worksheet in the Project window, paste your corrected Worksheet_Change script, and save.
Highly compatible with Microsoft Excel .xlsm and .xltm formatsBuilt-in VBA editor with familiar debugging and code-editing toolsFree, lightweight, and fast alternative to heavy spreadsheet programs
microsoft office alternative - wps office

Frequently Asked Questions

Why does standard Excel data validation not support multiple selections?

Standard Excel data validation is designed to only allow one selection per cell. To select multiple items from a single drop-down menu and append them together, custom VBA macros are required to capture the change event and combine the new selection with the existing text.

How do I stop the macro from repeating the exact same text?

You need to add a conditional check within your VBA Worksheet_Change event. By using the 'InStr' function, the macro can check if the newly selected item already exists in the cell's target string. If it does, the macro should skip appending it.

Why does the macro break my normal cell selection and lock up the sheet?

This happens when the macro modifies a cell, which instantly triggers the Worksheet_Change event again, creating an infinite loop. To fix this, you must add 'Application.EnableEvents = False' at the beginning of your script and 'Application.EnableEvents = True' at the end.