1

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.

aynber
  • 22,380
  • 8
  • 50
  • 63
astef
  • 8,575
  • 4
  • 56
  • 95
  • 1
    I cannot reproduce your problem but it sounds like a [Worksheet_Change](https://msdn.microsoft.com/en-us/library/office/ff839775.aspx) event macro could `.Unprotect` then set `Target.Locked = False` and `.Protect`. –  Sep 28 '15 at 09:16

1 Answers1

0

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.

astef
  • 8,575
  • 4
  • 56
  • 95