Locking Excel Cells conditionally

[hjgoldstein]

I posted this in Office forum but have been advised to post here, so apologies for the duplication

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.

I am led to believe that it may be achieved using VBA. Any help would be appreciated.

 

[SkipVought]

Hi,

Be careful what you wish for. This could become a sticky wicket. Once a value is entered, you will have to take special measures to "fix" values entered in error.

This is not a format. It is strictly code.

One possible approch might be to protect the sheet. Using the Worksheet_Change event, 1) the sheet would be unprotected 2) the target cell lock property would be changed to true, 3) the sheet would be protected. Any "correction" would involve 1) unprotect, 2) change the value, 3) protect.

Another approch might be to use the Worksheet_SelectionChange & Worksheet_Change events to simply prevent a value from being changed. If the target was empty, the change can occur. Otherwise the original value is replaced. Any "correction" would involve 1) disable events 2) make the change, 3) enabel events.

Your call.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WalkerEvans]

hjgoldstein,

Actually, a combination of both of Skip's suggestions might be the way to go: use the first for normal data entry, locking the cell as soon as the user moves out of it, and using the second to alert the user (via a message box) that the non-zero (or non-blank) value in the cell can not be changed except by submitting a request to project maintenance, which would likely be you. This would make any changes cumbersome, but it would (should?) also make users more careful when entering data.

My only concern is why you want this in the first place. In my experience it is always a good idea to allow users to correct their own mistakes. If there is not some really critical reason to lock-down the data the extra work doesn't seem worth the effort.

----------------------------------------------------------------------------------
[small]"How many liberal, forward thinking Kansans does it take to change a light bulb?"[/small]
"Both of them!"

 

[hjgoldstein]

Thanks to both of you. I will probably leave it and see how many problems occur. I can always re-visit it if necessary.