Conditional formatting/functions

[lynne_lowery]

Hi, I'm trying to format a worksheet so that when someone enters data,
this can't be deleted or changed by the subsequent users. I still want
users to be able to enter data in other cells though. Is there a way
to do this with conditional formatting or functions?

 

[Gord Dibben]

Only through event code which would lock each cell and protect the sheet after
entry.

Of course entering into the next cell would uprotect the sheet, lock that cell
and re-porotect the sheet.

Carry on entering, locking, protecting, unprotecting,locking...........

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
Me.Unprotect Password:="justme"
n = Target.Row
If Excel.Range("B" & n).Value <> "" Then
Excel.Range("B" & n).Locked = True
End If
End If
enditall:
Application.EnableEvents = True
Me.Protect Password:="justme", userinterfaceonly:=True
End Sub

Before adding the code to the sheet module, select all cells and
Format>Cells>Protection. Uncheck "Locked".

Add the code then start entering data in column B.


Gord Dibben MS Excel MVP