Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Locking Cells (All)

    I'm not talking about jailhouse cells.

    It seems that one cannot lock a cell without protecting the worksheet.

    Does Excel have a means to lock an individual cell, as Word has to lock an individual field?
    If not, that's a significant missing piece in Excel.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locking Cells (All)

    This is confusing to many Excel users.

    By default, all cells in an Excel worksheet are locked, but this only becomes effective when you protect the worksheet. The advantage of this approach is that it's easy for the developer to switch between protected and unprotected. This is often necessary during development, because there is a lot you can't do in a protected worksheet. You don't have to remember which cells you want to lock/unlock each time, you just protect/unprotect the worksheet.

    In many Excel "applications", you (as developer) will want all cells to be locked except for a few where the end user can enter data, so that the user can't mess with your carefully designed layout and formulas. In such cases, you only have to unlock the data entry cells, and then protect the worksheet.

    If you want to lock just one or a few cells, you'll have to select the entire worksheet (Ctrl+A), unlock them, then re-lock the cells you want to be locked, and protect the worksheet.

    As I wrote, this is confusing initially, but it works well once you get used to it.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locking Cells (All)

    You have my sympathy.

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locking Cells (All)

    Actually, I've done that and I use such sheets EVERY day, but I FORGOT.
    I've been distracted recently by a series of:

    1. On Monday, 28 Oct 2002, I went to bed at 05:30 Tuesday morning.

    2. I awoke only 3 hours later to find that I had no water in my house.

    3. I live in a condominium, in which each building has 4 units. Turned out that the recent new owner of an end unit left for Florida for the Winter that day and not only turned off the water in her unit, but also the water to the 4 unitys in our building and the 4 units in the next building.

    4. Later that day, I went to Sears, Roebuck to purchase a VCR. I've had Sears credit cards, off and on, since 1969, but Sears silently cancels them after a few years of non-use. I needed to apply for a new Sears credit card to get an additional 10% off the purchase and a $10 credit on my first statement, making the VCR very attractively priced. OK, to verify whether the Sears computer had any record of my previous account, the clerk entered my Social Security Number. Well, to our surprise, the computer came up with a different name using my SSN! Sears was able to use my driver's license info to get credit approval, which indicates that my credit record is clean. Later that day, I spoke to the Sears fraud dept. They confirmed the situation, but said not to worry.

    5. It gets worse! Next day, I found that my refrigerator had stopped coooling. As the critter was 18.5 years old, I ran off to Sears to buy a refrigerator. That too had 10% off if I used thec Sears credit card. As I did not yet physically have the credit card (I did recieve the card on 4 Nov), I used the sales receipt from the previous day to prove that I had an account. This was on a Wednesday, but refrigerator could not be delivered until Friday. Fortunately, I fiddled with the controls to get the old refrigerator cooling, so was able to survive and lost no food. The new refrigerator is likely quite a bit more energy efficient, so I'll save on electrical costs, not to mention is quite a bit larger, reducing te number of needed shopping excursions for food.

    6. Spent much of yesterday calling credit reporting agencies, Federal Trade Commission, and credit card companies, putting a fraud alert on my credit records to catch anybody trying to use my SSN to get credit.

    7. And, I now understand why folkes have trouble using VCRs. My old VCRs were made by RCA and had reasonable written instructions. The new VCR is from Panasonic, the instructions are awful, they almost make MSFT documentation look good!

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locking Cells (All)

    Unless I misunderstand the question, the macro below (from Legare Coleman) will do the job.
    The worksheet protection does not have to turned on to work.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    ' Source Woody's Lounge Author: Legare Coleman
    ' This prevents user from changing the cells without using
    ' protection and locking worksheet.

    If Intersect(Target, ActiveSheet.Range("B8084")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    MsgBox "You can't alter this cell.", vbExclamation + vbOKOnly
    Application.Undo
    Application.EnableEvents = True
    End Sub
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Locking Cells (All)

    Another solution (I think this too came from a Woody's post), without the need for VBA:
    -Define a name (Insert, name, define) called "Locked", that refers to =1.
    -Select the cells you need protected
    -Data, Validation, Select Custom, enter this formula: =Locked<>1
    -Select the Error alert tab and type a message (e.g. No Changes allowed to this cell).
    -OK
    To be able to edit the cells, set Locked to something other than =1.
    This solution will not prevent pasting data on top of the cells though.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locking Cells (All)

    Well, since I started this topic, to which I had already known, but forgotten the answer. Hmm, I wonder how much I "know" that I've forgotten?

    I figured out how to do this a few years ago, use it every day in speadsheets that keep tract of income, expenses, etc.
    In any case, the reason I'm looking now is that I'm doing some code that could take a while to run. Now, it runs from a button on the spreadsheet or a toolbar, but the Sub could easily be a function. However, I do not want the function to update when other cells change. So it is necessary to lock the already calculated cells.

    The code originally took over an hour to run, now it's down to about 3 minutes (on my PC). Don't want such code recalculating, n'est-ce pas?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locking Cells (All)

    <hr>I think this too came from a Woody's post<hr>

    Yes, one of mine.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    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: Locking Cells (All)

    I think this is an option in xl2002 (Office XP). I know that this version supports more protection choices. I only have xl97 and xl2000.

    Deb

Posting Permissions

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