Protect format and programming on cells and columns from change

[Scott]

My spread sheet is updated by a couple of persons regularly. They often use
cut and paste way to update the contents. Can someone advise me how to
protect the formatting and programming on cells and columns from change by
accidents.

Thanks,

Scott

 

[Shane Devenshire]

Hi Scott,

Exactly what do you mean by "programming on cells"? Data Validation, maybe?

If you allow them to copy and paste or cut and paste, they will affect the
formatting or data validation. You can program Excel so they can't cut and
paste. You could also program copy commands which only paste formulas.

Of course you could teach them how to use Copy, Paste Special, Formulas or
Paste Special, Value. These commands do not wipe out formatting or data
validation.

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[IanC]

By default, if you protect a worksheet, the entire sheet is protected
against change. To allow some cells to be changed, select them then got to
Format > Cells > Protection tab, then clear the "Locked" tickbox.

When you protect the sheet, only the unlocked cells can be changed.

To check you have the right cells unlocked, protect the sheet, select cell
A1 then repeatedly hit the Tab key. The highlighted cell will move to the
next available unlocked cell.

 

[IanC]

I think Scott's question was if there is a way to allow a user to
change the contents of a cell (such as by cut and paste) without
changing the formatting.

Say he had an unlocked cell formatted as "$0.00" and contained
"19.95". If one of his users copies data from a cell containing
"14.95" formatted as "0.0000", he wants the data to get stored but not
the formatting so that the result is "$14.95", not "14.9500".

I may be wrong about this.

But you may also be right! I took the reference to cell "programming" in the
original post to mean formulae. I think some clarification is needed from
Scott before anyone can be sure of exactly what he means.

 

[Gord Dibben]

Excel VBA has all the tools necessary to make the right thing happen no
matter what the user does.


Gord Dibben MS Excel MVP

 

[Scott]

But you may also be right! I took the reference to cell "programming" in
the original post to mean formulae. I think some clarification is needed
from Scott before anyone can be sure of exactly what he means.

Square Peg's understanding is right. I need to put some formatting, formula
and programming, i.i., display background colour to red if the figure
between 1 and 5, etc. I believe this is a common problem when a spreadsheet
is updated by different teams.

Scott