Allowing input, but restricting deletion

  • Thread starter Thread starter innovate
  • Start date Start date
I

innovate

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?
 
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:D 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
 
Back
Top