VBA & Macro Problems

How to Apply a Multiple-Selection VBA Drop-Down to Multiple Columns

Kushani Nimanthika Sep 30, 2026 871 views

Question details

The user wants to allow multiple selections from data-validation drop-down lists across non-contiguous columns (C, L, and M) using VBA, instead of limiting the macro to a single cell.

How to Apply a Multiple-Selection VBA Drop-Down to Multiple Columns

Product: Excel
Device & OS: not provided
Scenario: Modifying an existing Worksheet_Change VBA macro to append multiple selected values into a single cell across specific columns.
Observed behavior: The current macro is limited to a single cell, and when multiple selections are combined into one cell, Excel triggers a data validation error alert.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and back up your original VBA code before making modifications to the Worksheet_Change event.

Solution 1Recommended

Use the Intersect Function to Target Multiple Columns

Update the VBA macro to check if the modified cell falls within the specific columns (C, L, and M) rather than a single cell.

By replacing the single-cell reference with an Intersect check, you can apply the multi-select logic to entire columns. The code must also disable events during the change to prevent infinite loops.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor, and double-click the worksheet name where you want the drop-downs to function.

Prevent multi-cell selection errors

Add the condition 'If Target.CountLarge > 1 Then Exit Sub' at the beginning of your Worksheet_Change event to ensure the code only runs when a single cell is modified.

Add the Intersect check

Insert 'If Intersect(Range("C:C,L:M"), Target) Is Nothing Then Exit Sub' to restrict the macro's execution to columns C, L, and M.

Execute validation and append values

Write the logic to check if data validation exists, disable application events (Application.EnableEvents = False), undo the change to retrieve the previous value, concatenate the new value if it is not a duplicate, and finally re-enable events.

Use the Intersect Function to Target Multiple Columns

Restoring Events: Always include an exit handler that sets Application.EnableEvents = True. If your code errors out before reaching this line, macros will stop triggering in your workbook.

Solution 2

Disable Data Validation Error Alerts

Prevent the default error notification that appears when a cell contains appended multiple values that do not explicitly match a single item in the source list.

Easily Manage Macros and Drop-Downs in WPS Spreadsheets

WPS Spreadsheets offers seamless support for VBA and macros, allowing you to run complex multi-selection scripts just as you would in Microsoft Excel.

1. Open your macro-enabled workbook: Launch WPS Spreadsheets and open your .xlsm file containing the drop-down lists.
2. Access the Developer tools: Navigate to the Developer tab on the ribbon and click 'VBA Editor'.
3. Edit the Worksheet_Change event: Locate your target sheet in the project explorer and paste or modify your Intersect VBA code.
4. Save and test: Save your workbook and test the drop-down in columns C, L, or M to ensure multiple items append correctly.

Fully compatible with Microsoft Excel .xlsm files and VBA syntaxLightweight application with fast macro executionBuilt-in data validation tools for easy drop-down managementFree to download with a familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does a data validation error appear when I select a second item?

When the VBA macro combines your new selection with the existing one, the resulting string (e.g., 'Apple, Banana') does not exist as a single choice in your data validation source list. You must uncheck 'Show error alert after invalid data is entered' in the Data Validation settings to prevent this.

How can I remove items from a cell with multiple selections?

Because standard cell editing is overridden by the macro, you cannot simply backspace a single item. You need to select the cell, press the Delete key to clear the entire contents, and then re-select your desired items from the drop-down.

Can I limit the VBA multiple-selection code to specific rows instead of whole columns?

Yes. Instead of using whole column references like Range("C:C,L:M") in your Intersect check, you can specify exact ranges, such as Range("C2:C50,L2:M50"). The macro will then only execute for changes within those exact cells.

Excel & Data Problems