This is a problem that deserves a smarter solution than brute-force VBA event traps. The user who posted this question is clearly experienced enough to know that `Worksheet_Change` alone won't cut it, and they're right. The real challenge isn't just writing a macro that blocks pastes, it's writing one that doesn't break the very dropdown experience you're trying to protect.
The core tension here is something every spreadsheet builder hits eventually. You invest time in data validation, INDIRECT formulas, and dynamic named ranges to create clean, controlled inputs. Then one stray paste wipes out all of that structure. The user's instinct to block pastes on dropdown cells is correct, but the implementation details matter enormously. Detecting all dropdown types reliably means moving beyond `Cell.Validation.Type` alone. INDIRECT-based dropdowns and formula-driven lists don't always register as standard validation objects in the same way. You need a broader check: look for cells that have validation applied, but also scan for cells whose `Formula` property contains references to list-generating functions. This isn't a one-liner, but it is achievable with a helper function that evaluates the cell's formula string.
Distinguishing between a selection and a paste is where most attempts fall apart. The `Worksheet_Change` event fires for both, and by the time it runs, the damage is already done. A more reliable approach is to intercept the paste operation itself using `Application.CommandBars` or the `Worksheet_SelectionChange` event combined with a flag. Set a module-level Boolean to `True` when a dropdown selection is made via the validation dropdown button, then check that flag in `Worksheet_Change`. If the flag is `False` and the target cell has a dropdown, reject the change and restore the original value. This gives you a clean separation: selections set the flag, pastes don't.
The practical takeaway is that this isn't a job for a single event macro. It requires a small system of coordinated checks. But the payoff is worth the effort. Once you have a reliable paste block, your spreadsheets become genuinely protected, users can still interact freely with dropdowns, but the structure you built stays intact. That's the kind of control that transforms a fragile workbook into a tool you can hand off without fear. Build this once, reuse it everywhere, and stop spending your time cleaning up paste-induced messes.