locking cells without sheet protection

[flowergirl]

Hi,

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


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.

[Aflatoon]

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

[Bob Phillips]

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

[JWest]

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.

[mikerickson]

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.

[flowergirl]

Thanks a lot for your help

[Aflatoon]

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

[mikerickson]

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.

[Aflatoon]

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.