Cell protection
 

Is there a way of preventing formats being copied from other worksheets into
unprotected cells. Normal protection prevents formats being edited, which is
fine, but people can still copy data into the cells and thus change the
format.

No, unfortunately there is no built-in way to block format copy/pasting.
You have to disable copy and paste via code in all its manifestations
(menus, toolbars, keyboard shortcuts). Also cut and paste and using the
fill handle. Quite a pain.

--
Jim Rech
Excel MVP

"Vicar" wrote in message
...
| Is there a way of preventing formats being copied from other worksheets
into
| unprotected cells. Normal protection prevents formats being edited, which
is
| fine, but people can still copy data into the cells and thus change the
| format.

Jim Rech wrote...
No, unfortunately there is no built-in way to block format
copy/pasting.
You have to disable copy and paste via code in all its manifestations
(menus, toolbars, keyboard shortcuts). Also cut and paste and using
the
fill handle. Quite a pain.
....

While it may be a practical impossibility to prevent this, it may be
simpler to fix it after the fact using a Change event handler. Without
error checking or guarding against multiple cell selections, something
like


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cf As String

On Error GoTo ExitProc
Application.EnableEvents = False
Application.ScreenUpdating = False

cf = Target.Formula
Application.Undo
Target.Formula = cf

ExitProc:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Good thought, Harlan. That works with a copy. A cut and paste or a drag
and drop seems to get around it though.

--
Jim Rech
Excel MVP
"Harlan Grove" wrote in message
oups.com...
Jim Rech wrote...
No, unfortunately there is no built-in way to block format
copy/pasting.
You have to disable copy and paste via code in all its manifestations
(menus, toolbars, keyboard shortcuts). Also cut and paste and using
the
fill handle. Quite a pain.
...

While it may be a practical impossibility to prevent this, it may be
simpler to fix it after the fact using a Change event handler. Without
error checking or guarding against multiple cell selections, something
like


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cf As String

On Error GoTo ExitProc
Application.EnableEvents = False
Application.ScreenUpdating = False

cf = Target.Formula
Application.Undo
Target.Formula = cf

ExitProc:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Jim Rech wrote...
Good thought, Harlan. That works with a copy. A cut and paste
or a drag and drop seems to get around it though.
....

OP seemed pretty explicit about _copying_, and implied the worksheet
was protected. And it did seem like you were limiting your original
response to disabling copy operations. But you raise an interesting
generalization.

First off, Excel prevents 'move' operations from range to range within
protected worksheets. Next, it's impossible to drag & drop between
worksheets. That leaves cut & paste between worksheets.

This is where Excel is REALLY STUPID. Excel 2002, 2000 and 97 all allow
pasting ranges cut from other worksheets into unlocked ranges in
protected worksheets. Maybe this has been fixed in Excel 2003, but I
doubt it.

The good news is that if I make a slight modification to my Change
event handler, specifically, changing

Target.Formula = cf

to

If Not Target.Locked Then Target.Formula = cf

this prevents the 'move' operation as well as avoiding the apparently
untrappable runtime error when Excel believes Target is locked. Target
*becomes* locked after Application.Undo following *move* operations
even if both source and destination cells are unlocked whether the
worksheet is protected or not. In this case, this looks like a feature
since it can be used to prevent the interworksheet move operation even
though it won't retain the pasted values.

So, while my Change event handler won't retain values moved into cells
in protected worksheets, it does appear to prevent worksheet/workbook
corruption that Excel's apparently much weaker than previously believed
worksheet 'protection' fails to prevent.
Amazing what you can discover about Excel. What a piece of . . .

"Harlan Grove" wrote...
....
This is where Excel is REALLY STUPID. Excel 2002, 2000 and 97 all allow
pasting ranges cut from other worksheets into unlocked ranges in
protected worksheets. Maybe this has been fixed in Excel 2003, but I
doubt it.
....

OK, still stupid, but not uncommon.

FWIW, Lotus 123R5 and 123R97 both allow moving cells into unlocked cells in
protected worksheets (both /WGPE and sealed workbooks).

OpenOffice Calc also allows this, but OOo Calc has fundamentally different
semantics. If C2 contains the formula =B2+1 and C3 contains the formula
=B3+1, and if I cut B3 and paste into B2, the formula in C2 remains =B2+1
and the formula in C3 changes to =B2+1.

This has been really helpful - thank you - and works well when one cell at a
time is being copied/pasted from other sheets. Is there a way of achieving
the same when a block of cells is copied?

"Harlan Grove" wrote:

"Harlan Grove" wrote...
....
This is where Excel is REALLY STUPID. Excel 2002, 2000 and 97 all allow
pasting ranges cut from other worksheets into unlocked ranges in
protected worksheets. Maybe this has been fixed in Excel 2003, but I
doubt it.
....

OK, still stupid, but not uncommon.

FWIW, Lotus 123R5 and 123R97 both allow moving cells into unlocked cells in
protected worksheets (both /WGPE and sealed workbooks).

OpenOffice Calc also allows this, but OOo Calc has fundamentally different
semantics. If C2 contains the formula =B2+1 and C3 contains the formula
=B3+1, and if I cut B3 and paste into B2, the formula in C2 remains =B2+1
and the formula in C3 changes to =B2+1.