Protecting Ranges (XL 7 SR2)

Topic

New Reply

I used to appreciate SuperCalc’s way of protecting areas of my spreadsheets against inadvertent alteration:
I just selected a range of cells and used the Protect command (slash P). When another block of work was ready, selected it and did the same. What was already protected stayed protected. Supercalc kindly shows protected cells in contrasting colours. Simple. Friendly.

In Excel 97 , the only way I can find is to select everything in sight outside the range I want to protect (which can be very cumbersome), then click Format / click Cells / click Validation / tab Settings / check Allow Any Value / click OK, then click Tools / click Protection / click Protect Sheet / check Protect Worksheet For Contents / click OK.

When a new block of work is ready to be protected, Validation is inaccessible because of Protection, so I have to got to click Tools / click Protection / click Unprotect Sheet / click OK, then select the remainder of the open area where I may want to develop new work (excluding the first area protected plus the new area just ready to be protected) and go through the click Format / click Cells / click Validation / tab Settings / check Allow Any Value / click OK routine followed by the click Tools / click Protection / click Protect Sheet / check Protect Worksheet For Contents / click OK routine again.

Needless to say, Life’s too short for all that. A couple of macros could save a bit of time, but what a crazy situation!

The sensible-sounding sequence click Format / click Cells /click Protection / check Locked / click OK exists but doesn’t seem to be able to do anything at all.

Yet there must be some better way – yes?

Reply | Quote

Replies

In excel the default is to be “locked” / protected unless you specifically tell excel to NOT protect it. Protection is not “enabled” unless you protect the worksheet (tools – protection)

While doing development, I don’t understand why you need your worksheet protected. Just “unlock” the cells you want others to be able to change (when you do lock it) and develop away. When you are complete enable the protection and all else is protected.

Steve

Reply | Quote

Have you never, ever, made an unintentional change to a cell and not noticed it straight away?
Believe me, these things can happen, and they can cost you dear.

Reply | Quote

As being new to this lounge, I think your tone is a bit sharp isn’t it? I’m not clear on what your comment has to do with your need to understand how Excel protects cells? It’s obvious you’re a SuperCalc expert and now, for whatever reason, need to use Excel. There is always a learning curve with any new program and a resistance to change on your part (I’ve been there myself with WordStar and Word). Everyone on this great list is here to help you learn, don’t bite their heads off when they provide answers you don’t like. Data Validation is one of several features not available when a sheet is protected, oh well. You could write a macro to automate the unprotection steps you want when you update your sheets but no amount of ranting will make Excel work like SuperCalc.

As to your other post #208634 about customizing charts (labels, etc.) You can easily change everything about a chart usually by right-clicking on the area to be changed (legend, axis, grids, etc.). Sometimes it’s not easy getting the appropriate menu to appear since there are seem to overlap. The chart wizard doesn’t give you every possible thing that you change, no wizards do that. Wizards by definition let you select the most common features to get you started then you customize it after that.

Deb

Reply | Quote

OK – sorry – nobody else besides me makes clumsy mistakes and doesn’t notice straight away.
But this feature in EXCEL97- Format / Cells / Protection / Locked looks as though it was meant just for me.
I was hoping someone would tell me that in Excel 2000 it does work the way it looks, instead of being used after protecting the whole sheet to UNlock protection!
Meanwhile, back to the macro factory.
Perhaps what I’d better do is Protect everything and then Unlock the area where I next have work to do.

Reply | Quote

Yes, in XL97: Format – Cells – Protection tab – Locked will “lock” the cells (they are that way by default!) but as I said earlier, this is NOT ENABLED until you Protect the worksheet (tools – protection – protect sheet).

Also, regarding an earlier comment on validation NOT working with protection on is INCORRECT. Data validation DOES work with worksheet protection ENABLED, you just have to UNLOCK the cell (Format – Cells – Protection tab – UNLock) before the worksheet is protected.

Excel assumes that when you PROTECT a worksheet, you want the WORKSHEET protected and it protects EVERYTHING that you do NOT specifically unlock. Protecting prevents inserting, deleting, editing, etc since you have told excel that you do NOT want to change the sheet.

I develop with protection disabled (the default) Unlocking the cells that require editing in the model. When I share it with someone and then I will enable the protection.

There is no inherent way in excel to protect blocks of cells and then work with an unprotected portion the way you seem to want to work since the act of ENABLING protection, disables many of excel’s features.

Steve

Reply | Quote