Search code examples
excelvbacopy-pastespreadsheet-protection

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

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.

Solution

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