Protecting cells
Posted by Richard on September 17, 2001 12:22 PM
I have a requirement to allow entry of data into a cell that I wish to protect from alteration immediately after entry. The problem is that I then want to go on adding items below that entry - each time preventing alteration (or deletion of the row that it is on) so that at the end of the excercise, I have a complete list on the same worksheet exactly as it was entered at the time.
Any ideas anyone out there?
Posted by Damon Ostrander on September 17, 2001 4:06 PM
Hi Richard,
This is possible to do with a few lines of VBA. As an example, suppose that the cells you want this to apply to are in a range named "MyInputs". First, select this range and unlock just these cells (Format -> Cells -> Protection tab, uncheck Lock checkbox). Then protect the entire sheet with a password (in my example it is "mypassword"). Finally, put this code into the worksheet's event code area (right-click on the sheet's tab and select View Code).
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("InputRange")) Is Nothing Then
Unprotect "mypassword"
Target.Locked = True
Protect "mypassword"
End If
End Sub
If you don't want users to be able to see this code--and thus your password--simply also protect the VBproject (go to VBE, Tools -> VBproject properties.. -> Protection tab).
Happy computing.
Damon
Posted by Richard on September 18, 2001 5:48 AM
Damon
Fantastic - this really was just what I was looking for. Have tried it out very quickly and the result is exactly what I was hoping to achieve. Many thanks for your time and understanding.
Richard.
