View Full Version : locking cells without sheet protection

09-06-2011, 05:08 AM

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

ActiveSheet.Cells.Locked = False
ActiveSheet.Range("B3:D3").Locked = True
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.

09-06-2011, 05:32 AM
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
09-06-2011, 07:20 AM
Protection can be geared to specific operations, so you should be able to allow 'further operations'.

09-06-2011, 09:58 AM
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.

09-06-2011, 06:15 PM
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.

09-07-2011, 12:10 AM
Thanks a lot for your help

09-07-2011, 12:37 AM
That would not prevent deleting the contents or pasting over it, would it?

09-07-2011, 07:29 AM
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.

09-07-2011, 07:35 AM
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.