…specifically, in a worksheet that is protected, with only "select unlocked
cells" granted to users.

The situation: the users are not granted “format cells”, which has been
confirmed by attempting to manually apply bold, italic, or border changes.
The sheet is created from an Excel template that has all of the intended
formatting and protection in place.

Observed behavior: if a user pastes into an unlocked cell, the formatting of
the cell is overwritten with the formatting of the source. This has been
observed on several versions of Excel, including XP, 2003, and 2007.

The current environment requires that there be no macros, so are we stuck
with this quirky paste behavior unless/until macros are allowed?

hi
i have never accually dealt with your situation before and can't duplicate
your situation but.... have you tried edit>paste special?????
there are a number of options other than paste all.

regards
FSt1

"DCramlet" wrote:

> …specifically, in a worksheet that is protected, with only "select unlocked
> cells" granted to users.
>
> The situation: the users are not granted “format cells”, which has been
> confirmed by attempting to manually apply bold, italic, or border changes.
> The sheet is created from an Excel template that has all of the intended
> formatting and protection in place.
>
> Observed behavior: if a user pastes into an unlocked cell, the formatting of
> the cell is overwritten with the formatting of the source. This has been
> observed on several versions of Excel, including XP, 2003, and 2007.
>
> The current environment requires that there be no macros, so are we stuck
> with this quirky paste behavior unless/until macros are allowed?

Sorry, I forgot the repro steps.

"Paste Special..." works, which is expected since it works even if the sheet
isn’t protected, but that requires users, that do not use Excel every day in
this case, to never hit Ctrl-V (or Shift-Insert). It seems like the
protection is failing in the specific case of Paste.

1. Open Excel to a new, blank workbook.
2. Format Cell on A1
3. Set the font to Arial, Bold Italic, 12
4. Set the border to double-line outline
5. Set the protection, locked to unchecked
6. Click OK
7. Format Cell on C3
8. Set the font to Times New Roman, Normal, 8
9. Set the border to dotted, top and bottom
10. Set the protection, locked to unchecked
11. Click OK
12. Protect Sheet, clearing all options except “Protect worksheet…” and
“Select unlocked cells”
13. Enter “Test 1” in A1
14. Enter “Test 2” in C3
15. Attempt to format A1 and C3; observe that all formatting options are
disabled
16. Copy A1 and Paste in C3; observe that formatting of C3 is overwritten
17. Attempt to format A1 and C3; observe that all formatting options are
still disabled

"FSt1" wrote:

> hi
> i have never accually dealt with your situation before and can't duplicate
> your situation but.... have you tried edit>paste special?????
> there are a number of options other than paste all.
>
> regards
> FSt1
>
> "DCramlet" wrote:
>
> > …specifically, in a worksheet that is protected, with only "select unlocked
> > cells" granted to users.
> >
> > The situation: the users are not granted “format cells”, which has been
> > confirmed by attempting to manually apply bold, italic, or border changes.
> > The sheet is created from an Excel template that has all of the intended
> > formatting and protection in place.
> >
> > Observed behavior: if a user pastes into an unlocked cell, the formatting of
> > the cell is overwritten with the formatting of the source. This has been
> > observed on several versions of Excel, including XP, 2003, and 2007.
> >
> > The current environment requires that there be no macros, so are we stuck
> > with this quirky paste behavior unless/until macros are allowed?

Did you ever find a solution...I'm experiencing the same problem. The
copy/paste feature would never actually be needed in my worksheet, but if
someone accidentally pastes to one of the unlocked cells... it changes the
format of that cell and "breaks" the whole worksheet.

"DCramlet" wrote:

> …specifically, in a worksheet that is protected, with only "select unlocked
> cells" granted to users.
>
> The situation: the users are not granted “format cells”, which has been
> confirmed by attempting to manually apply bold, italic, or border changes.
> The sheet is created from an Excel template that has all of the intended
> formatting and protection in place.
>
> Observed behavior: if a user pastes into an unlocked cell, the formatting of
> the cell is overwritten with the formatting of the source. This has been
> observed on several versions of Excel, including XP, 2003, and 2007.
>
> The current environment requires that there be no macros, so are we stuck
> with this quirky paste behavior unless/until macros are allowed?

No; unfortunately, I have yet to find a means to effectively force something
akin to "Paste Unformatted Text" without using macros.

It really does seem like a bug, based on how I interpret the cell locking
feature. In case any reader missed them, the steps to reproduce the behavior
I'm seeing are in my first reply to FSt1.

"BMac" wrote:

> Did you ever find a solution...I'm experiencing the same problem. The
> copy/paste feature would never actually be needed in my worksheet, but if
> someone accidentally pastes to one of the unlocked cells... it changes the
> format of that cell and "breaks" the whole worksheet.
>
> "DCramlet" wrote:
>
> > …specifically, in a worksheet that is protected, with only "select unlocked
> > cells" granted to users.
> >
> > The situation: the users are not granted “format cells”, which has been
> > confirmed by attempting to manually apply bold, italic, or border changes.
> > The sheet is created from an Excel template that has all of the intended
> > formatting and protection in place.
> >
> > Observed behavior: if a user pastes into an unlocked cell, the formatting of
> > the cell is overwritten with the formatting of the source. This has been
> > observed on several versions of Excel, including XP, 2003, and 2007.
> >
> > The current environment requires that there be no macros, so are we stuck
> > with this quirky paste behavior unless/until macros are allowed?

This behavior is a serious limitation when using highly formatted worksheets.
With one step the user can trash the formatting even on protected worksheets.
I have a worksheet where I lock the cells except where I want to allow data
entry. I also disable 'Select Locked cells'. But in this mode I must leave
the data entry cells unlocked.

I have cells that have the background set and the formatting also gets
trashed if you use a 'Cut' of cells.

Is there a way, even with macros, to prevent the formatting from being
trashed by a cut and paste?

If not, I'm hearing that there is no way to prevent the formatting from
being trashed when you must have locked and unlocked cells.

> No; unfortunately, I have yet to find a means to effectively force something
> akin to "Paste Unformatted Text" without using macros.
>
> It really does seem like a bug, based on how I interpret the cell locking
> feature. In case any reader missed them, the steps to reproduce the behavior
> I'm seeing are in my first reply to FSt1.
>
> "BMac" wrote:
>
> > Did you ever find a solution...I'm experiencing the same problem. The
> > copy/paste feature would never actually be needed in my worksheet, but if
> > someone accidentally pastes to one of the unlocked cells... it changes the
> > format of that cell and "breaks" the whole worksheet.
> >
> > "DCramlet" wrote:
> >
> > > …specifically, in a worksheet that is protected, with only "select unlocked
> > > cells" granted to users.
> > >
> > > The situation: the users are not granted “format cells”, which has been
> > > confirmed by attempting to manually apply bold, italic, or border changes.
> > > The sheet is created from an Excel template that has all of the intended
> > > formatting and protection in place.
> > >
> > > Observed behavior: if a user pastes into an unlocked cell, the formatting of
> > > the cell is overwritten with the formatting of the source. This has been
> > > observed on several versions of Excel, including XP, 2003, and 2007.
> > >
> > > The current environment requires that there be no macros, so are we stuck
> > > with this quirky paste behavior unless/until macros are allowed?