none
Add restrictions to copy/cut/paste in Excel when a worksheet is protected

    Question

  • 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

    Monday, August 09, 2010 7:55 PM

Answers

  • 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.


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    • Marked as answer by Sally Tang Wednesday, August 11, 2010 5:59 AM
    Tuesday, August 10, 2010 7:55 AM

All replies

  • 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.


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    • Marked as answer by Sally Tang Wednesday, August 11, 2010 5:59 AM
    Tuesday, August 10, 2010 7:55 AM
  • 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

     

    Wednesday, August 18, 2010 7:03 PM
  • 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


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Wednesday, August 18, 2010 10:01 PM
  • 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

    Friday, August 20, 2010 5:50 AM
  • 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.
    Thursday, September 02, 2010 2:45 AM
  • 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.

     


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Thursday, September 02, 2010 6:39 AM
  • 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.

    Thursday, September 02, 2010 11:51 AM
  • 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.
    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Thursday, September 02, 2010 12:29 PM
  • 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.....
    Thursday, August 15, 2013 8:06 AM
  • 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

    Saturday, September 14, 2013 12:21 AM
  • 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 :)
    Friday, March 07, 2014 9:27 AM
  • 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!  


    Thursday, March 20, 2014 6:23 AM