14

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)?

5 Answers 5

13

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. To replicate a solved version of this problem create a new workbook, then:

1) On the Home Ribbon in the Styles section right click on the Normal box and click Modify. 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).

2) Select All cells, right click and choose format cells, and under protection check Locked.

3) Select A1 only, right click, choose format cells, and under protection uncheck locked.

4) Protect the worksheet

Now we should be back at the same point as the example in the question in terms of locked and unlocked cells but with a different Normal style.

Now when I paste text in from word or elsewhere the cell does not become protected.

1
  • 2
    After working several years with Excel I was shocked to find out about this "feature". I didn't even believe when my users told about it until I checked myself :) Glad you encountered this years before me :D
    – ZygD
    Sep 30, 2015 at 19:47
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.

2
  • 1
    Turns out it's not a bug. Have added what I think is a solution, and thought you might be interested. Dec 27, 2012 at 10:56
  • The pasting as plain text option (or Match Destination Formatting) is probably the best route to take if you've already set up your spreadsheet and can't face having to set it up all over again by editing the Normal style. However, it does rely on users of the spreadsheet knowing how to choose different paste options. In my experience, that's very few people!
    – user535673
    Feb 8, 2017 at 13:49
1

You select the unprotected cell in a protected sheet (the AtiveCell) in which you paste data from a web browser. After paste it becomes protected. That's not desirable:-)

I did this and it works!

In the sheet-code, in my case "Sheet1":

Sub Worksheet_Change(ByVal Target as Range)

  ...some irellevant code...

  ActiveSheet.Unprotect   
  ActiveCell.Locked=False

  ...code that protects Sheet1

Exit Sub
1

The previous answer no longer applies to the most recent version of Excel 365. The current version of Excel 365 now creates a new style named "Normal 2" or whatever sequence number it needs. It applies the new style which is locked instead of the modified normal style.

Here is the solution for Excel 365. Select the range of cells that you want others to be able to edit. Then, go to the "Review" tab. In the "Changes" section click on "Allow Users to Edit Ranges". Click the "New" button. Your selected range will auto populate with an auto-generated title. Make any manual changes you want. If you want people to be able to make changes only with a password, you can enter it here. I prefer for people not to use a password, so I click on the "Permissions" button, then on "Add". I want anyone to be able to edit without a password, so I enter "Everyone" as the user permitted to make edits. If you understand permissions, you can select one user or a set of users who will be able to make edits without password. Then just click "OK" until all the windows are closed. Protect your sheet and you are good.

0

Like everyone else on this page (and subject) have found this awkward. However, the best way (for me) to get around this is when pasting to "paste values" when inserting data and "paste special" - "hyperlink" when pasting links. I have done things this way as I usually have quite involved conditional formatting and I have to paste values as a matter of course (or the conditional formatting changes). Hope you find this of use.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .