Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Londonderry, Ireland, Northern
    Posts
    159
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Protecting Ranges (XL 7 SR2)

    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?
    All help gratefully received!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protecting Ranges (XL 7 SR2)

    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

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Londonderry, Ireland, Northern
    Posts
    159
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Protecting Ranges (XL 7 SR2)

    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.
    All help gratefully received!

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Ranges (XL 7 SR2)

    As being new to this lounge, I think your tone is a bit sharp isn't it? <img src=/S/ouch.gif border=0 alt=ouch width=15 height=15> 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. <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> 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. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

    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

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Londonderry, Ireland, Northern
    Posts
    159
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Protecting Ranges (XL 7 SR2)

    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.
    All help gratefully received!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protecting Ranges (XL 7 SR2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •