Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Protect Cells

  1. #1
    Board Regular Drdave1958's Avatar
    Join Date
    Mar 2002
    Location
    Sitting here inside myself
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a way to protect only certain cells on a sheet from being changed (e.g. formulas)?

    Thanks, Dave

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-23 10:19, Drdave1958 wrote:
    Is there a way to protect only certain cells on a sheet from being changed (e.g. formulas)?

    Thanks, Dave
    Hi Drdave1958:
    Select the cell you want to protect -- then FORMAT|CELLS|PROTECTION|Locked
    Now the Locking of the cells will take effect only after you protect the worksheet. If you do not protect the worksheet, locking of the cells has no effect whatsoever.
    HTH

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    Board Regular Drdave1958's Avatar
    Join Date
    Mar 2002
    Location
    Sitting here inside myself
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Yogi but what I'm after is to allow the end user to input data into cells other than the ones that contain my formulas. Am I missing something here?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-23 10:39, Drdave1958 wrote:
    Thanks, Yogi but what I'm after is to allow the end user to input data into cells other than the ones that contain my formulas. Am I missing something here?
    Select your entire sheet (or the cells you anticipate will be used), click on Format-Cells-Protection and uncheck the 'locked' box. Then select the cells you don't want to be changed and click on Format-Cells-Protection and recheck the 'locked' box. Then click on Tools- Protection- Protect Sheet and enter a password if you want. HTH.

  5. #5
    Board Regular Drdave1958's Avatar
    Join Date
    Mar 2002
    Location
    Sitting here inside myself
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thats the ticket!
    Thanks Mudface

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This helped me as well.

    But is their a way of selecting all the cells containing formula's in a sheet so that you can protect them in one go ?.

    Hope you can help.

  7. #7
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Obviously, hold down the CONTROL key as you select each one. Then follow the procedure above. That may be tedious if you have many cells. But I have done it with as many as 100 cells. Works fine.
    - old, slow, and confused
    ... but at least I'm inconsistent -

    (retired Excel 2003 user, 3.28.2008)

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Shades
    I have a lot more than 100cells.
    I have currently done it by selecting the full column or row of data that contains the formula's.
    I am looking for a hidden feature (well hidden to me anyway) that must exist such as 'Select all' then you can choose Formulas.
    If it doesn't currently exist perhaps Bill will oblige!

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Harry,

    try this :

    . select the whole sheet (top right button to highlight everything)
    . right click within the highlighted range
    . format cells
    . protection
    . unlock them all (uncheck the "locked" box)
    . click "ok"

    you've just unprotected every cell in the sheet

    .select the whole sheet (top right button to highlight everything)
    . edit (from menu bar)
    . goto
    . special
    . formulas (formulae)
    . numbers / text / logicals / error should all be checked
    . hit "ok"

    you have now selected all the formulae in your sheet

    . right click inside one of those highlighted cells
    . format cells
    . protection
    . lock them all (check the "locked" box)
    . click "ok"

    you've just protected every cell in the sheet
    that was highlighted, with the only highlighted cells being those which contain formulae

    ergo, your formulae are protected

    but we need to protect the sheet in order for this to come into force :

    . tools
    . protection
    . protect sheet
    . add a password if you're in Dirty Harry mode today
    . click "ok"

    try adding numbers to blank cells, should be okay.. now try amending a cell you know to have a formula in it and you should get a "freeze...LAPD" warning






    :: Pharma Z - Family drugstore ::

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris
    Just what I needed. Perfect !
    No need to ask Bill now.
    Cheers

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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