Protected Excel workbook broken after pasting data from a "newer" workbook

Question

Given an excel workbook with an unlocked cell in a protected worksheet.

If I copy a cell from another workbook which was opened after target workbook, and paste it to the unlocked cell, it becomes locked and I can't do anything with it except undo the paste action.

On the other hand, if source workbook was opened before the target, copy-paste works as expected - target cell remains editable.

I've reproduced this on excel 2007 and 2010.

What am I asking is to reproduce the problem and advise how to handle this issue with VBA to avoid locking cells by users.

Answer 1

Following @Jeeped advise, I wrote this script and it works:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Unprotect Password:="pwd"
Target.Locked = False
Sh.Protect Password:="pwd"
End Sub

But there's a side effect. Undo cache will be cleared each time worksheet changes.