How to prevent pasting over cells with dropdown lists (simple, INDIRECT, dynamic, or formula-based) using Excel VBA?

Hi Excel experts,

I’m trying to create a VBA macro that prevents users from pasting over cells that have dropdown lists, regardless of the dropdown type. These dropdowns include:

• Simple static lists
• INDIRECT-based dropdowns
• Dynamic named ranges
• Dropdowns created with formulas

The goal is to allow users to freely select options from the dropdown, but block any kind of paste or overwrite on those cells, since pasting breaks the validation or introduces invalid data.

So far, I’ve tried event macros using Worksheet_Change and checking Cell.Validation.Type, but I struggle with:

• Correctly detecting all dropdown types (including INDIRECT and formula-based)
• Distinguishing between normal dropdown selection and pasting
• Preventing pasting without blocking valid dropdown selections

Has anyone successfully implemented a VBA macro or alternative method that:

1. Detects all dropdown types reliably,
2. Allows dropdown selection without issues,
3. Blocks any pasting or overwriting over those dropdown cells?

Any example code, advice, or best practices would be greatly appreciated!

Thanks in advance!