shacho
asked on
Blocking Format Changes on Paste
This has always baffled me. If you protect a sheet and disallow format changes, you cannot change the formatting of an unlocked cell - as expected. But if you copy and paste, you can overwrite the formatting and even the validation. That's a very nasty loophole. I've always coded around this by intercepting the paste command, but I hate having to do this every time. Is there another, easier way to solve this problem?
Mike
Mike
ASKER
It's approximately what I do now, though my version allows some controlled pasting by extracting data from the clipboard and depositing it as a type-appropriate string inside the cell or merged cell block. That works, but the nasty pitfall in this approach is that it causes permanent changes to the interface that must be reversed or re-initialized whenever you switch active windows or close the file. If the application crashes (and this happens as we all know) those changes stick. Opening and closing the file will fix it, but that's not something an ordinary user will think to do if CTRL-V just suddenly stops working.
Agreed.
You might get a little more protection putting all this in a class module w/ application events...
You might get a little more protection putting all this in a class module w/ application events...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No solution reached
Yogesh Gupta did a blog a while back http://www.yogeshguptaonline.com/2009/04/macros-in-excel-disable-cut-copy-paste.html to show us how to disable cut/copy/paste = which you could tailor to certain worksheets, or the entire book. This approach may be more robust than others by preventing copy/paste rather than having to trap it and deal with it. I must confess I've not tried it, but I'm in the same boat as you, having had to deal with this with data validation. I will be trying it out next time I have to deal with this issue.
Perhaps its a bit easier on the coding than what you currently do?
Dave