Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Changes
-- Hi. I would like to be able to set up excel 2003 with the following features. I would like people to be able to enter data into the spreadsheet, but not be allowed to change anything when it is entered. Even if Joe enters data I do not want Joe to be able to change his data. I know that I can track changes. Is there any other ideas people have about how this can be done? What about special permissions? Maybe special permissions on the folder containing the spreadsheet? Start with the sheet unprotected and all the cells unlocked. This event macro will look for changes in column A. Once a cell has been changed, that cell is locked: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Protect Contents:=False Target.Locked = True ActiveSheet.Protect Contents:=True End Sub Hi. Wow! That's cool. Thanks so much. Can you make that work for the entire sheet? -- vze2mss6 I never got any further response. I was hoping somebody else could pick up the ball with the rest of this code or give me an alternative. Thanks -- vze2mss6 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Changes
Try this.
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:IV"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Protect Contents:=False Target.Locked = True ActiveSheet.Protect Contents:=True End Sub Gord Dibben MS Excel MVP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Changes
Interesting that you should suggest that Gord,
If Intersect(Range("A:IV"), Target) Is Nothing where else could it be? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Try this. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:IV"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Protect Contents:=False Target.Locked = True ActiveSheet.Protect Contents:=True End Sub Gord Dibben MS Excel MVP |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Changes
I see that now Sandy.
Too quick on the trigger. Removal as you have shown is best. Gord On Mon, 20 Aug 2007 22:37:25 +0100, "Sandy Mann" wrote: Interesting that you should suggest that Gord, If Intersect(Range("A:IV"), Target) Is Nothing where else could it be? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Changes
Hi.
Thanks to Sandy and Gord It works! Next issue: if somebody wants to change a cell all they have to do is click on unprotect worksheet. Am I correct? Is there any way to prevent this? -- vze2mss6 "Gord Dibben" wrote: I see that now Sandy. Too quick on the trigger. Removal as you have shown is best. Gord On Mon, 20 Aug 2007 22:37:25 +0100, "Sandy Mann" wrote: Interesting that you should suggest that Gord, If Intersect(Range("A:IV"), Target) Is Nothing where else could it be? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Changes
Next issue: if somebody wants to change a cell all they have to do is
click on unprotect worksheet. Am I correct? I don't really understand what you mean by that. As your code stands all they have to do is unprotect the sheet then change the cell. You could add a password to the sheet protection but then all they would have to do is look at the code to get the password. There is nothingin Excel that you can do that a determined person cannot undo. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "joesf16" wrote in message ... Hi. Thanks to Sandy and Gord It works! Next issue: if somebody wants to change a cell all they have to do is click on unprotect worksheet. Am I correct? Is there any way to prevent this? -- vze2mss6 "Gord Dibben" wrote: I see that now Sandy. Too quick on the trigger. Removal as you have shown is best. Gord On Mon, 20 Aug 2007 22:37:25 +0100, "Sandy Mann" wrote: Interesting that you should suggest that Gord, If Intersect(Range("A:IV"), Target) Is Nothing where else could it be? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Changes
Hi.
Thanks Sandy If I could add a password to protect the sheet that would be great. The particular folks I want to lock out won't look at the code. I doubt it at least. How do I set a password for the sheet? In the past, when I have set one you still didn't need the password to change a cell. All you had to do was click on unprotect sheet. I must be doing something wrong? Help? -- vze2mss6 "Sandy Mann" wrote: Next issue: if somebody wants to change a cell all they have to do is click on unprotect worksheet. Am I correct? I don't really understand what you mean by that. As your code stands all they have to do is unprotect the sheet then change the cell. You could add a password to the sheet protection but then all they would have to do is look at the code to get the password. There is nothingin Excel that you can do that a determined person cannot undo. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "joesf16" wrote in message ... Hi. Thanks to Sandy and Gord It works! Next issue: if somebody wants to change a cell all they have to do is click on unprotect worksheet. Am I correct? Is there any way to prevent this? -- vze2mss6 "Gord Dibben" wrote: I see that now Sandy. Too quick on the trigger. Removal as you have shown is best. Gord On Mon, 20 Aug 2007 22:37:25 +0100, "Sandy Mann" wrote: Interesting that you should suggest that Gord, If Intersect(Range("A:IV"), Target) Is Nothing where else could it be? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop Changes
Hi.
No need Sandy. I think I've got it now. Thanks for everything. -- vze2mss6 "Sandy Mann" wrote: Next issue: if somebody wants to change a cell all they have to do is click on unprotect worksheet. Am I correct? I don't really understand what you mean by that. As your code stands all they have to do is unprotect the sheet then change the cell. You could add a password to the sheet protection but then all they would have to do is look at the code to get the password. There is nothingin Excel that you can do that a determined person cannot undo. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "joesf16" wrote in message ... Hi. Thanks to Sandy and Gord It works! Next issue: if somebody wants to change a cell all they have to do is click on unprotect worksheet. Am I correct? Is there any way to prevent this? -- vze2mss6 "Gord Dibben" wrote: I see that now Sandy. Too quick on the trigger. Removal as you have shown is best. Gord On Mon, 20 Aug 2007 22:37:25 +0100, "Sandy Mann" wrote: Interesting that you should suggest that Gord, If Intersect(Range("A:IV"), Target) Is Nothing where else could it be? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To Stop #Div | Excel Worksheet Functions | |||
1 becomes 10, how can I stop this | New Users to Excel | |||
To Stop or Not to Stop | Excel Discussion (Misc queries) | |||
How do I stop other circles in other cells to stop selecting? | Excel Worksheet Functions | |||
How do I stop other circles in other boxes to stop selecting? | Excel Worksheet Functions |