sheet protected but cells "cleared of all" become locked

[Guest]

Hi,

I have a sheet where columns headers and row labels are locked but the rest
of the sheet is open for editing.

However, random staff members occasionally empty a cell using |Edit|Clear|All|

This also resets the protection formatting for the cell, and it becomes
protected. Is there a work around or is staff education the best option?

This is all in Excel 2003.

Cheers,

Glen

 

[Joerg Mochikun]

You have to protect the worksheet. The 'locked' format property of cells
have no effect if the sheet itself is not protected.

Joerg

 

[Dave Peterson]

The default style (unless you change it) is to have the cell locked. And when
someone uses Edit|Clear All, the cell inherits this default style.

But you can change the default style for this workbook.

Open the workbook
Format|Style|Select Normal from the dropdown
Click Modify
On the Protection tab, uncheck Locked.

If you (as a developer) ever use clear all, you'll want to remember that the
"lockedness" of that cell may not be what you want.

ps. Styles live in workbooks. So if you have multiple workbooks that need
this, you'll have to change each of them.

pps. You may want to remind the users that it's easier to clear cells of the
contents by hitting the delete key on the keyboard.

 

[Guest]

Thanks,

that worked a treat. And in all my years, to think I've never been in that
menu option before. Today is another day I have learnt something new

I don't suppose there is an equally elegent way to define a conditional
format as part of the normal style?

Cheers,

Glen

 

[Dave Peterson]

I don't think so. But it may be worth suggesting to Microsoft including
conditional formatting in styles.