Locking Excel Cells After Data Has Posted

[Stretch055]

Hi All,

New to the group. I am something of an Excel expert, but I've run into an issue I cannot resolve. I am hopeful I can find a solution to a problem I am having currently.

In prior versions of Excel, I used to be able to unlock a cell (under the Protection tab in the cell format window) in Excel, protect the worksheet, and then the user could paste data into the cell that would lock the cell when all the formatting copied over.

Unfortunately, this ability has been lost in Excel 365 ProPlus (at least, I can't get it to work again). Do you have any suggestions on how this can be regained? We utilize this function to track payments from a source system while preventing the user from adjusting the numbers.

Stretch055

[Andy Pope]

Did the cell lock itself using a macro that was run when the change event fired?

[FDibbins]

...In prior versions of Excel, I used to be able to unlock a cell (under the Protection tab in the cell format window) in Excel, protect the worksheet, and then the user could paste data into the cell that would lock the cell when all the formatting copied over....

I have been using excel back to ver 97, and have never seen a setting to automate that functionality, are you sure there was not some code running in the back ground?

[Stretch055]

No, there was no macro or program running in the background. It was an older version of Excel; I tested the functionality of this myself before distributing to the end users.

Is there a macro I can run automatically to achieve this? How would I write the macro? My macro experience is next to zero.

[AliGW]

Which older version? I've been using Excel for nearly 30 years and this has not existed as a native setting in any version I have used. Maybe you had an add-in installed?

You'd need VBA to mimic the behaviour. Shall I move the thread to that section?

[Stretch055]

Yes, please move this to the VBA thread.

[AliGW]

Done. Out of interest, which version had this special functionality to which you are referring?

[Stretch055]

Excel 2003.

The steps were:
1) Unlock a cell
2) Protect the worksheet
3) Paste data from another workbook into the protected sheet. This then set the cell back to locked status and prevented further editing.

[FDibbins]

I used 2003 for years, and never saw that functionality. I created systems, protected them and then distributed them to users, so Im sure I would have seen that option, if it were native.

[menem]

If I'm not wrong, in Excel 2003 and before (97 or XP) will copy all attribute from original cell include lock/unlock.
And after Excel 2007 and later will exclude lock/unlock attribute while copy/paste.

Regards.


Note:
In Excel 2007 and later, will copy lock/unlock attribute if protect is off.
If protect is on (sheet is protected) will not copy lock/unlock attribute.

You may need VBA to slove this problem.