Stop Changes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

-- 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
 
Remove the lines:
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Click to expand...

But be aware that if someone opens the sheet with macros disabled then the
code will not run.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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
 
Interesting that you should suggest that Gord,
If Intersect(Range("A:IV"), Target) Is Nothing
Click to expand...

where else could it be?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
I see that now Sandy.

Too quick on the trigger.

Removal as you have shown is best.


Gord
 
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?
 
Next issue: if somebody wants to change a cell all they have to do is
click
on unprotect worksheet. Am I correct?
Click to expand...

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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?
 
Back
Top