Solved

Blocking Format Changes on Paste

Posted on 2011-09-05
5
170 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:shacho
  • 3
  • 2
5 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 36486779
I'm afraid some type of trap is necessary to prevent the user from copy/pasting.  We have to do the same thing with data validation cells, to avoid the restrictions being copied over.

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
0
 

Author Comment

by:shacho
ID: 36486866
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.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36490941
Agreed.

You might get a little more protection putting all this in a class module w/ application events...
0
 

Accepted Solution

by:
shacho earned 0 total points
ID: 36533907
Well, I guess there just is no other way to do it.  Oh well.  Excel isn't really intended for building forms.
0
 

Author Closing Comment

by:shacho
ID: 36555870
No solution reached
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office 2016 Excel Issue 4 26
Excel Score Formula 5 49
Excel - find text within text? 1 24
Excel 2016: auto-number column of merged cells. 47 23
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now