Office Version:  Office 2007; Operating System:Windows 7;  Program:Excel

I need to be able to select via a macro or some other way the restrictions listed below on copy/cut/paste.  The worksheet will be protected.  The goal is to allow a user to have the capability to type in data or copy-paste-values only in the unlocked cells.  I wish to block a user from changing the formatting or pasting a formula in an unlocked cell via copy or cut and paste.  This is necessary to maintain the worksheet/workbook integrity when multiple users have access to it.

1.      Disable cutting from unlocked cells

2.     Allow ONLY copy/paste value from any cell to any unlocked cell.   

The user needs to be able to select locked cells.

Ideally, the user can copy-paste-values from that locked cell an an unlocked cell.  The ability to copy from the locked cell is not an absolute need but being able to select the locked cell is very important due to the large size of the worksheet.

The locked cells will have their formulas hidden.

I entered the question in the website below but received no feedback.  The Microsoft tech support group said I should contact this IT Pro forum.

http://answers.microsoft.com/en-us/office/default.aspx#tab=1

Thanks

Unfortunately it is not easy; there are lots of individual items to deal with.

You can use Application.OnKey to disable (or repurpose) the key combinations (e.g. Ctrl+X, Ctrl+V, Shift+Del, Shift+Insert ) that you don't want used.  Typically you would do this in Workbook_Open and reverse it in Workbook_BeforeClose (and you need to save the workbook if necessary within Workbook_BeforeClose to prevent the user from cancelling the Save Changes message after your code has done its work).

In Excel 2003 you could use VBA to disable or make invisible the menu items and toolbar buttons that would do actions you didn't want the user to be able to carry out.  But in 2007 you have to write XML using the custom UI editor and save it in the XLSM file.  A good site for information on how to work with the ribbon in 2007 is www.rondebruin.nl

If you get most of the way there and get stuck on some point, do come back.

It would be good if MS extended the protection model to prevent cut and pasting other than values - it is a common requirement.  I will go now and make that suggestion to the developers.

Thanks for the reply.  My skill level is not sufficient to do the code.  I requested m/s services group do the code under their payment schedule but was told they also do not have the capability to do it by the tech.

Can you suggest how I might get the code written?

thanks

 

I guess you need to find someone who knows how to do it and has time available to help you.
It is not appropriate for people to use this forum to sell their services so you will probably not get any offers here.

You could try contacting an MVP by email and enquiring if they could help you, or if they can put you in touch with someone who can.

You could try searching the net for "Microsoft Excel MVP" "disable cut".
One article you might reach is this one which could give some help in creating the code
http://www.jkp-ads.com/articles/catchpaste.asp

Hi Bill, many thanks for the response.  I followed your link to Jan's site and entered the question in that thread in a slightly different way to see if he can help.  Next step would be the MVP search as you suggest.

thanks again

It would be good if MS extended the protection model to prevent cut and pasting other than values - it is a common requirement.  I will go now and make that suggestion to the developers.

OMG! Are you serious?

Of course it's a common requirement for Excel to work as advertised. If I uncheck the box that says "allow all users to format cells", then I expect all users to not be able to format cells.

The box does not say "unless they use copy-paste, in which case allow all users to change any formatting".

Thousands of people have been searching for years for a way to prevent pasting in Excel from changing formats without needing to enable macros, and you make it sound like you just stumbled across this new idea that only needs to be suggested to the developers.

I think you may have violated Microsoft guidelines by even mentioning this serious Excel flaw on an official Microsoft website. You could lose your job.

a) I am not employed by Microsoft.
b) I have been aware of this issue for most of the 25 years that I have been using Excel and, like everyone else, have either lived with it or painfully worked around it.
but
c) I have never discussed the issue with the development team, and a quick check indicated that it was not already on the suggestion list that I have access to, being an Excel MVP.
 
So rather than continuing to do nothing, I have made a practical suggestion as to how the issue could be dealt with.
We will see in the next version whether any change is made as a result.

I hope this meets with your approval.

 

I apologize for the tone of my previous post -- sometimes my frustration at Microsoft gets the better of me.

I find it especially infuriating that this flaw has existed in Excel for decades and yet Microsoft's support website does not mention it at all. They discuss locking cells and protecting worksheets as if it works fine, as if this serious flaw does not exist.

Of course Microsoft knows that developers want to protect their carefully-formatted worksheets because Excel provides the "allow all users to format cells" option, and of course Microsoft knows the option does not work because everyone who uses Excel knows how to copy-paste.

It seems so simple to enforce paste-values-only without needing to rely on macros, but after so many years I've pretty much given up hope.

Thanks for doing what you can to improve Excel.

As well as submitting the suggestion I intend to raise the issue when I am next with the developers in person, in about 6 months time.

Any news regarding this poor issue ? I'm try to protect validation on excel 2007, can't use VBA and it makes me "nuts" that a simple paste can just revoke any rules I apply.....

You can un-check the 'select locked cells' option on the dialog box that appears when you choose 'Protect Sheet'. The locked cells can't be selected while the sheet is protected and thus cannot be copied.

Wish that will help

You can un-check the 'select locked cells' option on the dialog box that appears when you choose 'Protect Sheet'. The locked cells can't be selected while the sheet is protected and thus cannot be copied.

Wish that will help

Thanks for that, this did the job for me :)

While Tamer's solution works for preventing a locked cell from being selected it does not fix the issue.  If the user cuts and pastes an unlocked cell then all the formulas that refer to the cut cell position will update resulting in a corrupted worksheet.   Why is it taking so long fro MS to resolve this failry basic requirement.   'Locked' means 'locked' - no cut and paste changes to formatting values or formula - surely it can'be that hard.   Back to seeking an elegant macro solution but that means macro security issues on the network :-( which many organisations are not prepared to do.

Example:

Consider a simple array formula {=SUM(J24:J27*$G$24:$G$27)}  in locked Cell J28 where rows 24 to 27 are unlocked data entry cells.  If the value in Cell J27 is cut and paste into Cell J26 (which one of my clients did today) the formula in Cell J28 is automatically and unwantedly changed to {=SUM(J24:J26*$G$24:$G$27)} resulting in an #N?A error as the array ranges are no longer the same size!  

• Edited by John at Waiben Thursday, March 20, 2014 6:24 AM Typo

So.... I have found 12 YEARS of people complaining about this online!!!!!   Has Microsoft or anyone else ever solved it completely (including copy/paste, cut/paste, drag and drop, from shortcuts or menus)!!!!!!!!!!!!!!!!!   I don't care what the solution looks like.... as long as it works!  

MICROSOFT if you haven't solved this, WAKE UP from your 12 year sleep!!!   We should NOT have to endure this!   THIS IS A BUG in your implementation of the check box to protect formatting.    It is a COMMON use case to copy and paste and cut and paste.   If this doesn't work, your format protection feature doesn't work......  that is called a BUG!   FIX IT for EVERYONE's sake!...... please......

If you fix this in the next version, it will be worth the upgrade price ALONE.....

• Edited by FairlyTechy 17 hours 15 minutes ago