Why does pasting into an unprotected cell in Excel protect it?

Question

While researching the answer to this question, I stumbled across a rather odd behavior in Excel related to unprotected cells in protected worksheets.

To see what I mean, create a new workbook, unprotect its first cell (A1) and protect the worksheet. The only cell that text can be entered into now should be A1. Next go to some other application (for instance your browser) and copy some text from there. Select the first cell by clicking it once (don't double-click or paste into the formular bar) and paste the text into it.

Not only will this potentially change the formatting of the cell, it will also set its protected flag. Thereafter, there is no way to ever change that cell's content again without removing the worksheet's protection (or undoing the paste operation, of course).

Why is that? Is this desired behavior or a bug in Excel 2010 (the version I used to try this)?

Answer 1

I don't know why it is happening but as far as i'm concerned, that is a very big bug. :-(

It looks like any time Excel tries to paste in text formatting along with the text it is relocking the cell. If you choose Match Destination Formatting as your paste option; paste from a plain text editor like Notepad; or paste from another workbook in the same instance of Excel then the cell is still unlocked afterwards. If you do a regular paste from any other application that has text formatting, even another instance of Excel, then Excel is locking your unlocked cell.

The formatting issue as least has a work-around where you can copy the correct formatting from another cell and paste it back in the messed up cell.