VBA & Macro Problems

How to Enable Multiple Selections in Excel Drop-Down Lists via VBA

John WilsonJohn Wilson Nov 10, 2026 871 views

Question details

The user needs to configure data validation columns in Excel to allow multiple items to be selected from a single drop-down list using VBA.

How to Enable Multiple Selections in Excel Drop-Down Lists via VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Creating advanced forms or data entry sheets where multiple tags or categories (like Letters and Notices) must be selected within the same cell.
Observed behavior
Standard Excel data validation drop-down lists restrict users to selecting only one item per cell, overwriting previous selections.
Before you start

Ensure that the Developer tab is enabled in your spreadsheet ribbon and that you have a list of data validation items already prepared.

Solution 1Recommended

Implement a Worksheet_Change VBA Event

Use a VBA macro to intercept drop-down selections and append them to the existing cell text instead of overwriting it.

Because Excel does not natively support multiple selections in drop-down lists, a VBA Worksheet_Change event is required. This script monitors specific columns for changes and combines the old and new values with a delimiter, such as a comma.

1
Open the Worksheet Code Module

Right-click the specific sheet tab at the bottom of your workbook where the drop-down lists are located, and select View Code from the context menu.

2
Insert the VBA Script

In the Visual Basic for Applications window, paste your multiple-selection VBA script into the blank code window. Ensure the script targets the correct columns (e.g., Target.Column = 2 for column B).

3
Close the VBA Editor

Click the X in the top right corner of the VBA window or press Alt + Q to close the editor and return to your worksheet.

4
Test the Drop-Down List

Click on a cell with data validation and select an item. Click the drop-down arrow again and select a second item. Both items should now appear in the cell, separated by a comma.

5
Save as a Macro-Enabled Workbook

Go to File > Save As, choose your destination, and select Excel Macro-Enabled Workbook (*.xlsm) from the format dropdown. This ensures your VBA code is not lost when you close the file.

Implement a Worksheet_Change VBA Event
Macro Security: When reopening the file in the future, remember to click 'Enable Content' in the yellow security warning bar at the top of the screen so the script continues to function.
Efficient Spreadsheet Management

Manage Advanced Drop-Down Lists with WPS Spreadsheet

WPS Office Spreadsheet provides excellent support for advanced data validation and macro execution, allowing you to easily implement and manage multiple-selection drop-down lists. Enjoy a familiar interface that handles complex macro-enabled spreadsheets effortlessly.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the data validation drop-down lists.
  2. 2. Access the Developer tools: Navigate to the Developer tab on the top ribbon to view, edit, or manage macros and VBA code.
  3. 3. Apply Data Validation: Go to the Data tab and select Data Validation to set up or modify your base drop-down lists before running your scripts.
Full compatibility with Microsoft Excel (.xlsx and .xlsm) formatsRobust support for advanced data validation and custom form controlsLightweight application that opens large, macro-heavy files quicklyFamiliar ribbon interface requires no learning curve
microsoft office alternative - wps office

Frequently Asked Questions

Can I enable multiple selections in a drop-down list without using VBA?

No, standard Excel does not have a built-in feature to select multiple items from a single data validation drop-down list. A VBA macro is strictly required to intercept the selection and append it to the cell's existing contents.

Why is my VBA code not working when I reopen the Excel file?

This usually happens if the file was saved as a standard Excel Workbook (.xlsx) instead of a Macro-Enabled Workbook (.xlsm), or if macros are disabled in your Trust Center settings. Ensure you save with the correct .xlsm extension and click 'Enable Content' when opening the file.

How do I remove an accidentally selected item from the multiple-selection cell?

If you are using a standard append VBA script, you will need to manually click into the cell (or press F2) and delete the unwanted text and its accompanying comma. Advanced VBA scripts can be written to toggle selections on and off, but manual deletion is the most common workaround.

Does the VBA multiple-selection code work in Excel for the Web?

No, Excel for the Web does not support running VBA macros. To use multiple-selection drop-down lists that rely on Worksheet_Change events, you must open the workbook in the desktop version of Excel or a compatible desktop suite like WPS Office.