locking cells without sheet protection

Hi,

I am using the following code to lock certain cells in the worksheet.

Code:

---

ActiveSheet.Cells.Locked = False
ActiveSheet.Range("B3:D3").Locked = True
ActiveSheet.Protect
ActiveSheet.EnableSelection = x1UnlockedCells

---

But this is protecting the worksheet and not allowing further operations.
Is there a way to lock cells from editing without using sheet protection?

Thanks for your help.

Not really, although you could use a worksheet_change event to undo the changes if any cells in a specific range are altered.

Protection can be geared to specific operations, so you should be able to allow 'further operations'.

You can use Data Validation on the cells you want "locked". Type the information you want into the cell, select the cell, then go to Data --> Validation --> Allow (Custom) --> Make the formula equal to the cell you just typed your information into (eg. =A12). This isn't fool proof, but it might be enough to simply deter editing.

Another alternative using Data Validation is to enter the data or formula in the cell and then put Validation on the cell with the custom formula =FALSE and uncheck the ignore blanks box.

Thanks a lot for your help

Mike,
That would not prevent deleting the contents or pasting over it, would it?

The method I suggested will not protect against Paste or VBA entry into the cell.

It will protect against Backspace+Enter, it will not protect against Clear or ClearContents.

On the plus side, the protected cell can be Cut and pasted whereever one wants it.

It also will not protect against the delete key which is generally more prevalent than Backspace and Enter, I suspect (except perhaps in the Mac arena). In any event at least the asker has many options.