MrExcel Publishing
Your One Stop for Excel Tips & Solutions

protecting cells

Posted by Hassan Soyer on August 28, 2001 9:39 AM

If there any way that i can tell excel to protected/lock cells that only have a value.

Ie. I want to be able to make the cell locked only once a user has entered data.

I.e they cannot go and then edit the cell once saved.

Any help would be appriciated.

Posted by Jerid on August 28, 2001 11:25 AM

Hassan, try this

1) Select all the cells in one worksheet, select Format, Cells, click the protection tab and uncheck the locked property.
2) Protect the sheet (without a password for this example)
3) Put this code in the code module for your sheet

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Value <> vbNullString Then
ActiveSheet.Range(Target.Address).Locked = True
End If
End Sub

What this will do is lock and protect each cell as soon as you enter data into it.

Change as needed.