Protect Data after entry

[boatsc]

Once data is entered into a cell I need it to be protected (automatically)
and not able to be changed or deleted.

I am using this for a auditing receivables and once I see the receivable on
the spreadsheet, I do not want anyone to be able to change or remove it.
Need to also be able to add additional receivables to spreadheet.

A formula that if a cell has data it becomes protected was my thought but ???.

[Gord Dibben]

A formula cannot do anything but return results.

You could use event code in the worksheet.

First, select Column B and Format>Cells>Protection. Uncheck "locked"

If your column of receivables is other than B, edit the

If Target.Cells.Column = 2 Then to something other than 2

Copy/paste the code below to the worksheet by right-click on sheet tab and "View
Code"

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
ActiveSheet.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
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

To cap it off close the sheet module by clicking on the "x".

Right-click on the workbook/project and select VBAProject Properties then
"Lock project for viewing"

You can password protect this module so prying eyes can't see it and obtain
the password("justme").

You now save the workbook and close Excel....the VBAProject locking won't come
into effect until Excel is closed out fully.

If the users enable macros and if no one of them knows how to crack a
VBAProject password, you should be good to go.


Gord Dibben MS Excel MVP


On Sat, 5 Aug 2006 12:21:01 -0700, boatsc <boatsc@discussions.microsoft.com>
wrote:

>Once data is entered into a cell I need it to be protected (automatically)
>and not able to be changed or deleted.
>
>I am using this for a auditing receivables and once I see the receivable on
>the spreadsheet, I do not want anyone to be able to change or remove it.
>Need to also be able to add additional receivables to spreadheet.
>
>A formula that if a cell has data it becomes protected was my thought but ???.