Prevent user from amending data
 

I have a sheet which has to remain unprotected.

Column A on this sheet is made up of the numbers 1, 2 and 3 (on their own).

A
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3

What I need to do is prevent users changing any of these values, the sheet
cannot be protected.

Any suggestions gratefully received.

Gareth

Prevent user from amending data
 

Give your range of cells that should be "protected" a nice name. (I used
Protected.)

Then right click on the worksheet tab that should have this behavior and select
View Code. Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("Protected")) Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:
With Application
.EnableEvents = False
.Undo
End With

errHandler:
Application.EnableEvents = True

End Sub


If the user opens the workbook with macros disabled (or just turns off event
handling), then it fails miserably.

Gareth wrote:

I have a sheet which has to remain unprotected.

Column A on this sheet is made up of the numbers 1, 2 and 3 (on their own).

A
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3

What I need to do is prevent users changing any of these values, the sheet
cannot be protected.

Any suggestions gratefully received.

Gareth

--

Dave Peterson