Hi all,

Is it possible to allow input into a spreadsheet, but at the same time
making sure that data that is inputted is impossible to delete?

Hope you guys can help,
Roland

You may be able to do something with some sort of worksheet event. But I'd be
worried about typos. How would you go about fixing those if the cell was locked
after I finished my initial input?

innovate wrote:
>
> Hi all,
>
> Is it possible to allow input into a spreadsheet, but at the same time
> making sure that data that is inputted is impossible to delete?
>
> Hope you guys can help,
> Roland

--

Dave Peterson

Not without some event code that would prevent the deletion after input.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that module.

Alt + q to return to Excel window.

CTRL + a to select all cells.

Format>Cells>Protection........uncheck "locked"

As written, the event code will operate on any cell in columns A to lock the
cell after data input. Options for other ranges can be provided to suit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column < 5 Then
ActiveSheet.Unprotect Password:="justme"
If Target.Value <> "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

To prevent users from seeing the code and password, protect the project from
viewing.

Alt + F11 to open Visual Basic Editor.

Select your workbook/project and right-click>VBAProject
Properties>Protection>Lock for Viewing. Apply a password and save/close the
workbook.

When re-opened, code will be unviewable or uneditable.


Gord Dibben MS Excel MVP

On Thu, 07 Jun 2007 10:56:30 -0700, innovate <(E-Mail Removed)> wrote:

>Hi all,
>
>Is it possible to allow input into a spreadsheet, but at the same time
>making sure that data that is inputted is impossible to delete?
>
>Hope you guys can help,
>Roland