VBA & Macro Problems

Fix Excel Drop-Down List Cannot Select Multiple Items in XLSM

WPS Content Manager Sep 28, 2026 872 views

Question details

The user is unable to select multiple items from a drop-down list in an Excel macro-enabled workbook (XLSM) because the underlying VBA code required for this functionality is blocked.

How to Fix Excel Drop-Down List Not Selecting Multiple Items in XLSM Files

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to select multiple options from a data validation drop-down list that relies on a custom VBA script to append selections.
Observed behavior: The drop-down list only allows a single selection, overwriting the previous choice, because security settings are preventing the macro from running.

Before you start

Ensure that your XLSM file is from a trusted source, as enabling macros from unknown origins can expose your computer to malicious scripts and security risks.

Solution 1Recommended

Unblock Macros for Downloaded Internet Files

If the file was downloaded from the internet or an email attachment, Windows blocks its macros by default. Unblocking the file properties will allow the VBA code to run.

Microsoft has implemented strict security measures that block macros in files originating from the internet. You must manually unblock the specific file before Excel will allow the custom drop-down VBA code to execute.

Open File Properties

Close the workbook in Excel. Navigate to the folder where the XLSM file is saved, right-click the file, and select 'Properties' from the context menu.

Check the Unblock Box

At the bottom of the General tab, look for a Security section that says 'This file came from another computer and might be blocked'. Check the 'Unblock' box next to it.

Apply and Reopen

Click 'Apply' and then 'OK'. Reopen the file in Excel and check if the drop-down list now allows multiple selections.

Unblock Macros for Downloaded Internet Files

Macro Enabled: Once unblocked, the VBA Worksheet_Change event handler for the drop-down list will trigger correctly upon selection.

Solution 2

Add File Folder to Trusted Locations

Adding the folder where the XLSM file is saved to Excel's Trusted Locations will automatically bypass the macro block for all files within that directory.

Solution 3

Check Macro Settings in Trust Center

Verify that your overall Excel settings are not configured to silently disable all macros.

Free Microsoft Office alternative

Try WPS Office for Powerful VBA and Spreadsheet Management

If you frequently encounter restrictive macro blocks or complicated security settings in Excel, try WPS Office. It provides robust support for VBA and XLSM files within a lightweight, user-friendly interface.

1. Download and Install: Download WPS Office for free from the official website and follow the simple installation prompts.
2. Open your XLSM file: Launch WPS Spreadsheet and click 'Open' to select your macro-enabled workbook directly.
3. Enable Macros: Click 'Enable Macros' if prompted by the security warning, and seamlessly use your multiple-selection drop-down list.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv)Built-in VBA editor for seamless macro execution and developmentFree and lightweight alternative to Microsoft OfficeFamiliar user interface requiring no learning curve

Frequently Asked Questions

Why doesn't Excel allow multiple selections in a drop-down list by default?

Excel's native Data Validation feature only supports single selections. To select multiple items, users must employ custom VBA code (typically the Worksheet_Change event) to append new selections to the existing text.

How do I know if my VBA macro is being blocked by Excel?

You will usually see a yellow security warning bar below the ribbon stating 'Macros have been disabled', or a red bar stating 'Security Risk: Microsoft has blocked macros from running because the source of this file is untrusted.'

Is it safe to add my Downloads folder to Trusted Locations?

No. It is highly discouraged to add your default Downloads folder to Trusted Locations, as any malicious script downloaded from the internet could execute automatically. Always move trusted files to a dedicated, secure folder first.

Excel & Data Problems