Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
disallow edit in a cell with data
Hello All,
I use Office 2003. I have a worksheet where I want to prevent users from changing data once it is entered in a blank cell. Is it possible with a macro where it should 1) save the worksheet once data is entered 2) ask for a password if someone tries to edit a cell with an entry in it. Thanks in advance. Rashid Khan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
disallow edit in a cell with data
First "Unlock" all cells on the sheet
Select all cells, the little square that intersects row/col headers Format, Cells, Protection, Locked - uncheck Rt-Click the sheet tab, view-code to open the worksheet module and paste in the following Private Sub Worksheet_Change(ByVal Target As Range) Me.Unprotect "abc" Target.Locked = True Me.Protect Password:="abc", Contents:=True End Sub You could ignore the password entirely, depending on what user is allowed to access to. So what happens if user makes a mistake when entering the data ??? Regards, Peter T wrote in message ... Hello All, I use Office 2003. I have a worksheet where I want to prevent users from changing data once it is entered in a blank cell. Is it possible with a macro where it should 1) save the worksheet once data is entered 2) ask for a password if someone tries to edit a cell with an entry in it. Thanks in advance. Rashid Khan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
disallow edit in a cell with data
On Feb 10, 8:26*pm, "Peter T" <peter_t@discussions wrote:
First "Unlock" all cells on the sheet Select all cells, the little square that intersects row/col headers Format, Cells, Protection, Locked - uncheck Rt-Click the sheet tab, view-code to open the worksheet module and paste in the following Private Sub Worksheet_Change(ByVal Target As Range) * * Me.Unprotect "abc" * * Target.Locked = True * * Me.Protect Password:="abc", Contents:=True End Sub You could ignore the password entirely, depending on what user is allowed to access to. So what happens if user makes a mistake when entering the data ??? Regards, Peter T wrote in message ... Hello All, I use Office 2003. I have a worksheet where I want to prevent users from changing data once it is entered in a blank cell. Is it possible with a macro where it should 1) save the worksheet once data is entered 2) ask for a password if someone tries to edit a cell with an entry in it. Thanks in advance. Rashid Khan- Hide quoted text - - Show quoted text - Thanks for your help. Editing is not allowed if someone makes a mistake, he/she should contact the admin for help. Can it prop up a message box saying to contact the admin for the password/help? Regards Rashid Khan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
disallow edit in a cell with data
wrote in message news:a4b65605-3617-4fbc-9116-
Thanks for your help. Editing is not allowed if someone makes a mistake, he/she should contact the admin for help. OK, so the code I suggested should work for your purposes. Maybe start by only "unlocking" cells user can enter data once only. Did you try the sugegstion. Can it prop up a message box saying to contact the admin for the password/help? Probably, but in what scenario, your question is not clear. Regards Rashid Khan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
disallow edit in a cell with data
On Feb 10, 9:33*pm, "Peter T" <peter_t@discussions wrote:
wrote in message news:a4b65605-3617-4fbc-9116- Thanks for your help. *Editing is not allowed if someone makes a mistake, he/she should contact the admin for help. OK, so the code I suggested should work for your purposes. Maybe start by only "unlocking" cells user can enter data once only. Did you try the sugegstion. Can it prop up a message box saying to contact the admin for the password/help? Probably, but in what scenario, your question is not clear. RegardsRashidKhan Sorry my system was down. Actually the solution you provided works but mine is a shared workbook and it does not work with shared workbook However, if a msg box can prop up when the user tries to edit the cell. Thanks Rashid Khan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
disallow edit in a cell with data
I don't have experience of shared workbooks and cannot test what works or
not. Maybe if there is someway to update changes in both users the original suggestion will work. Regards, Peter T wrote in message Sorry my system was down. Actually the solution you provided works but mine is a shared workbook and it does not work with shared workbook However, if a msg box can prop up when the user tries to edit the cell. Thanks Rashid Khan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disallow cell data entry which autopopulates | Excel Worksheet Functions | |||
How do I get data validation to disallow specific entries | Excel Worksheet Functions | |||
Disallow cell entries | Excel Discussion (Misc queries) | |||
Disallow sorting of data. | Excel Programming | |||
disallow edit in used rows on save | Excel Programming |