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

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