Locking Cells (All)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

You have my sympathy.

Reply | Quote

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: =Locked1
-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
[Fmr MS MVP - Word]

Reply | Quote

[indent]

---

I think this too came from a Woody’s post

---

[/indent]

Yes, one of mine.

Reply | Quote

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(“B80:D84”)) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox “You can’t alter this cell.”, vbExclamation + vbOKOnly
Application.Undo
Application.EnableEvents = True
End Sub

Reply | Quote

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?

Reply | Quote

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

Reply | Quote