Password Protection - paste to unprotected cell locks the cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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.)
 
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
 
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?
 
Ahhh. From MSWord. A significant detail!!! <vbg>

Try changing the Normal Style default from locked to unlocked.
 
Bingo! Thanks Dave and apologies for holding back on the Word information.

Have a good weekend
 
Back
Top