I am looking for a solution that will caution the users from copying unwanted formats (Locked) into my protected workbook.
The workbook is locked with a number critical, structure-providing cells locked on each worksheet with all other cells unlocked. When people use this file to do work, they routinely need to copy information from other Excel files. If they are lazy or don't remember, they do a standard Paste, the copied/target cells will be locked and I have to manually lock these for the users.
Many samples codes found online offer enforced Paste Special by Values or Formulas. I do not want to do that. The reason being, my users need to be able to drag & drop, double-click copy, right click copy/paste, and do normal copy/paste (formats & all) among unlocked cells within this workbook. It will be too restricting to enforce a Paste Special - Values for all pasting actions. Instead, I'd like to give them a warning when such Paste action is detected.
Currently my code will trap a Ctrl+V and responds with warning and a Paste Special Values only. I'm not able to stop people from clicking the Regular Paste Button on the Ribbon and the Right-Click menu (red part of the attached screenshots). Is there a way to detect whether people click the standard Paste Button or the little arrow for Paste Special options? I'd like to disable the standard Paste parts (red parts), or at a minimum give users a warning when they click the Red part, and only enable the blue parts; or in the case of red warning, directing users to use the blue parts.
Is it possible?
My code is here. Thank you for reading!
Bookmarks