Locking cells conditionally 1

[hjgoldstein]

Is it possible for a cell in Excel 2003 to be formatted so that it becomes locked after data entry?

I have been provided with a model which would benefit from this approach.

Thanks for any advice.

 

[SkipVought]

Hi,

Not without VBA code.

Post your question in Forum707, if you wish to pursue this kind of a solution.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

There is a very messy way to achieve the same aim, but I really wouldn't recommend this:

Pick a region of your worksheet in which you would like the user to enter their data. Unlock these cells.

If the data to be entered are text, pick a piece of null text (e.g. "-") and enter it in these cells.

Now find another region of the worksheet in which to mirror the data. This area remains locked.

Turn on iteration (Tools-Options-Calculation tab)

In the "mirror" arear of the worksheet, enter equations of the form: =IF(B2=0, A2, B2), where B2 is the cell into which you are entering the equation, and A2 is the cell whose data should be mirrored here. For text entry, replace 0 with your null text "-".

Now protect the worksheet.

The idea is that while cell B2 contains a null, it will reflect A2, but the moment A2 is non-null, this value will appear in B2, which from then on will be equal to itself, and therefore unchanging. Of course the user can change their entry later, but it won't affect the mirror, and you can use the mirror area version of the data.

You can, of course, put the mirror-area in some hidden columns so the user doesn't see it, but this carries the risk that a user will re-enter data, and won't be aware that their first entry was binding.

But seriously, this is a messy solution, and I'm not sure it's sensible to stop people from entering data twice. No one is a perfect typist.

 

[GSCaupling]

The "mirror" idea might be messy, lionelhill, but it reflects some pretty clever thinking.

GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[buitenhek]

Skip is right, not without VBA.
Another bothersome way:
1. user input cell = format - cell - protection check mark only hidden for the user input (not locked)
2. user input cell = format - conditional formatting - turn either a cell color on or off depending on whether the value =""
3. lock the sheet to allow only unlocked cells
4. user input cell = set the value to =""

When they enter data, the cell color will change, and they cannot see the baseline value of ="".
All this said, they can still copy that cell to another sheet and see the input value...so...if you have a smart user...

Regards
Peter Buitenhek
ProfitDeveloper.com

"Never settle for a job well done...always look for cost cutting measures