VBA & Macro Problems

Prevent Duplicate Selections in Excel Multi-Select Dropdowns via VBA

Partner Editor Oct 9, 2026 868 views

Question details

The user needs to prevent a VBA-based multi-select dropdown from duplicating existing selections when adding custom text via an "Other" option.

How to Prevent Duplicate Selections in a Multi-Select Excel Dropdown

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using a VBA macro to allow multiple selections from a dropdown list, including capturing custom text when the user selects "Other".
Observed behavior: Existing selections are duplicated when "Other" is chosen and custom text is entered. The user also wants selections separated by line breaks and custom text to retain its trigger label.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your security settings before modifying the code.

Solution 1Recommended

Update VBA Code to Handle Custom Inputs and Line Breaks

Modify your Worksheet_Change event to use line breaks as delimiters and properly catch custom inputs before appending them to the cell.

By using vbLf as your delimiter instead of commas, you force each selection onto a new line. Catching the 'Other' selection in an If statement before appending prevents the macro from reading the cell's old value twice, thus avoiding duplicates.

Open the VBA Editor

Press Alt + F11 to open the VBA Editor, then double-click the specific Worksheet name in the Project Explorer where your dropdown is located.

Update the Delimiter

Locate the concatenation logic in your Worksheet_Change event code. Change the delimiter variable from a comma to vbLf (e.g., NewVal = OldVal & vbLf & Target.Value).

Add an InputBox for 'Other'

Add an If statement to check if Target.Value = "Other". If true, use an InputBox to capture the user's custom text and store it in a variable.

Append the Custom Text

Append the custom text with your desired prefix (e.g., "Other - " & CustomText) to the existing cell value instead of just the word 'Other'.

Enable Wrap Text

Return to your worksheet, select the target cells with the dropdown, and click 'Wrap Text' on the Home tab so the vbLf line breaks display correctly.

Update VBA Code to Handle Custom Inputs and Line Breaks

Formatting Best Practice: Keep unrelated data types, such as dates or long descriptions, in separate columns to maintain clean formatting and prevent your multi-select macro from interfering with other cell formats.

Advanced Macros in WPS Office

Create and Manage VBA Dropdowns in WPS Spreadsheet

WPS Spreadsheet offers full support for VBA macros, allowing you to seamlessly create multi-select dropdowns, manage InputBoxes, and automate complex tasks without compatibility issues.

1. Enable Developer Tools: Open WPS Spreadsheet and go to the Developer tab. If it is not visible, enable it in the Options menu.
2. Access the VBA Editor: Click on 'VB Editor' in the ribbon or press Alt + F11 to open the macro programming environment.
3. Insert Your Code: Double-click your target worksheet in the Project window and paste your multi-select dropdown VBA code.
4. Save as Macro-Enabled: Save your file as a Macro-Enabled Workbook (.xlsm) to preserve the VBA functionality for future use.

Full support for VBA and Excel macro (.xlsm) filesSeamlessly execute Worksheet_Change events and custom scriptsFree, lightweight, and highly compatible with Microsoft Excel formatsIntuitive developer tools for easy debugging and code management

microsoft office alternative - wps office

Frequently Asked Questions

How do I display line breaks in Excel cells after using vbLf in VBA?

Even if your VBA code inserts a line break using vbLf, the cell will not display it properly until you enable Wrap Text. Select the affected cells, go to the Home tab on the ribbon, and click 'Wrap Text'.

Can I trigger an InputBox for multiple specific dropdown options?

Yes, you can use a Select Case statement in your Worksheet_Change event to evaluate the selected value. This allows you to trigger an InputBox for "Other", "Custom", or any other specific text strings you define in the code.

Why does my multi-select VBA macro duplicate the first entry?

This usually happens if the code appends the new target value to the old cell value, but fails to clear Application.EnableEvents temporarily. This causes the Worksheet_Change event to trigger itself in an infinite loop or re-evaluate the cell's contents. Always wrap your cell updates by setting Application.EnableEvents to False before the change, and True afterward.

Excel & Data Problems