Only allow users to enter new data
 

I have an excel 2003 sheet on network drive.
Users at the moment enter data. but at the same time then can delete update
etc all other rows.
I only want them to be able to enter new rows/data. But do not want them to
update/delete/change etc on the data that already exists.
Any help with code/macro will be appreciated.
Thanks.

Murad

Only allow users to enter new data
 

Murad,

Well, you could format all the blank cells to be unlocked, then protect the sheet (allowing the user
to select unlocked cells only, but with no password) and use event code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub

Copy the code, right-click the sheet tab, select "View Code" and paste the code in the window that
appears.

Note that once the user enters something, they will not be able to edit it.

Alternatively, you could lock cells with entries when the file is saved: put code like this into the
codemodule of the Thisworkbook object:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Sheet1")
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeConstants).Locked = True
.Protect
End With
End Sub

HTH,
Bernie
MS Excel MVP


"Murad Sheikh" wrote in message
...
I have an excel 2003 sheet on network drive.
Users at the moment enter data. but at the same time then can delete update
etc all other rows.
I only want them to be able to enter new rows/data. But do not want them to
update/delete/change etc on the data that already exists.
Any help with code/macro will be appreciated.
Thanks.

Murad

Only allow users to enter new data
 

Thanks Bernie,
It sounds good; I will have a go and let you know.
Thanks for the quick response.
Murad

"Bernie Deitrick" wrote:

Murad,

Well, you could format all the blank cells to be unlocked, then protect the sheet (allowing the user
to select unlocked cells only, but with no password) and use event code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub

Copy the code, right-click the sheet tab, select "View Code" and paste the code in the window that
appears.

Note that once the user enters something, they will not be able to edit it.

Alternatively, you could lock cells with entries when the file is saved: put code like this into the
codemodule of the Thisworkbook object:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Sheet1")
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeConstants).Locked = True
.Protect
End With
End Sub

HTH,
Bernie
MS Excel MVP


"Murad Sheikh" wrote in message
...
I have an excel 2003 sheet on network drive.
Users at the moment enter data. but at the same time then can delete update
etc all other rows.
I only want them to be able to enter new rows/data. But do not want them to
update/delete/change etc on the data that already exists.
Any help with code/macro will be appreciated.
Thanks.

Murad