Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
users must enter value = or than a certain value | Excel Worksheet Functions | |||
how do i force users to first enter data in one cell before anythi | Excel Discussion (Misc queries) | |||
Protect cells from other users w/out me having to enter pw | Excel Discussion (Misc queries) | |||
Defining specific areas for users to enter data | Excel Discussion (Misc queries) | |||
Not allowing users to enter data into certain cells if another cell is empty | Excel Programming |