Password Protection - paste to unprotected cell locks the cell

[Guest]

Dear All

I have a sheet which has selected cells protected to ensure users enter
their alphanumerical data in the required cells. If a user types data into
the cells they can correct their entry but if they paste directly into the
cell (Ctrl + V), the cell formatting is altered and the cell becomes
protected. Q.1 - Why should this be?

I have advised users to use the formula bar (F2) if they want to paste data
but I'm sure some will persist with their previous method. Q.2 - how can I
overcome what appears to be an unwanted bonus feature?

BTW, the protection I'm using is select unlocked cells, format cells, insert
rows and insert hyperlinks.

 

[Guest]

I'd look to see if there was any Worksheet_Change code that is triggered when
this occurs. Right click on the affected tab and View Code.

HTH,
Barb Reinhardt

 

[Guest]

Barb, I didn't say but there are no macros in this workbook.
Any other clues, please?

 

[Dave Peterson]

I used xl2003 for my testing.

I created a worksheet that had locked and unlocked cells. I protected the
worksheet using the options you specified.

Then I added another worksheet and added some text in locked cells (the
worksheet was unprotected).

And I copied (ctrl-c) from one of those cells and pasted to an unlocked cell on
that protected worksheet.

The pasted cell was still unlocked.

So I couldn't duplicate the problem you describe.

But ...

If the users are clearing the cells (Edit|clear|all), then they could be
resetting the protection to the workbook's default--specified in the Normal
Style.

If that's possible, then you may want to change the Normal Style so that the
default is not locked.

Format|style|Normal (style name)|modify button|protection tab

(this is a workbook setting.)

 

[Peo Sjoblom]

It must be because you have allowed the users to format cells, that might
mean that if you copy a cell that is locked and paste using ctrl + v it will
paste the locked formatting as well,. That's my guess. What happens if you
paste special as values instead of Ctrl + v? If that works you might want to
test by remove format cells when you protect the sheet



--
Regards,

Peo Sjoblom

 

[Guest]

Dave, thanks for this.
I'm also using 2003.

Maybe the difference (which I didn't say) is that users are copying and
pasting from Word. Users aren't clearing the cell contents first, just
pasting directly into a selected cell.
As I said, pasting to the formula doesn't produce the same anomaly.

Any more clues, please?

 

[Dave Peterson]

Ahhh. From MSWord. A significant detail!!! <vbg>

Try changing the Normal Style default from locked to unlocked.

 

[Guest]

Bingo! Thanks Dave and apologies for holding back on the Word information.

Have a good weekend