Can you prevent Copy-Paste from changing formatting without macros

[DCramlet]

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

 

[FSt1]

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]

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

 

[BMac]

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]

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.

 

[DocBrown]

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.