Excel 2007 : Cells Lock After Pasting into Unlocked Cells on Protected Sheet

Anytime I paste from a website into unlocked cells on a protected worksheet, the cells lock up and become non-editable. This is even after I check off every box in the sheet protection prompt that asks what the users of the worksheet are allowed to do to a protected sheet. (the options are like Select Locked Cells, Select Unlocked Cells, Format Cells, Format Columns, etc.).

Any ways around this?

I know there is paste special, but there are alot of people using this worksheet. I don't want to do a macro, because you cannot undo after it.

Hello there... two years on in this thread and I'm also running into the same problem.
I still see no solution.

Can anyone give us the answer... or at least then tell us there is no solution?

Why dont you post a thread for your question? It will get more views and faster responses.

Looking into some other sources I found the solution (thanks to Alex Andronov)

It is actually expected behavior (well expected by Microsoft, not necessarily by users) which is the good news. And there is a quite simple work around which is the even better news.
When you paste in a piece of formatted text the cell gets given the "Normal" style and then has the required formatting layered on top.
The "Normal" style by default has protection set to locked. So this is why it is being applied.

The solution is to modify the "Normal" style:

1. On the Home Ribbon in the Styles section right click on the Normal box and click Modify.
2. Press the Format button and on the protection tab uncheck Locked. (Remember this only applies to this spreadsheet, but that all cells will now be unprotected rather than protected).
3. Re-instate protection on all cells (format cells, and under protection check Locked).
4. Select the range you need to be unprotected, right click, choose format cells, and under protection uncheck locked.
5. Protect the worksheet.

Done

Originally Posted by arlu1201

Why dont you post a thread for your question? It will get more views and faster responses.

Because the title of this thread is exactly describing the problem, you get it as the first search result in google.
Having an answer on this thread will speed things up for people looking for the answer.
Besides, I found the answer somewhere else and posted it here.