Protect cell data

[Gareth]

Column A on my sheet has the following data validation:

A2:A300 can only be 1
A301:A500 can only be 2
A501:700 can only be 3

Thes numbers are already on the sheet when the user gets it, what I am
trying to do is stop them changing it.

I discovered today that if the sheet is sorted the validation goes to pot.

Is there any other way to avoid the value in column A from being changed,
the sheet cannot be protected.

Thanks in advance.

Gareth

 

[J.E. McGimpsey]

One way:

Put this in your worksheet code module (right click on the worksheet
tab, choose View Code and paste the macro into the module window
that opens):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Range("A2:A300").Value = 1#
Range("A301:A500").Value = 2#
Range("A501:A700").Value = 3#
End If
End Sub

 

[Gareth]

This seems to work, when I alter a number it changes back but then Excel is
'frozen'. I have to 'Ctrl + Break' to interrupt the code.

What's happening?

 

[J.E. McGimpsey]

Actually, it's just taking a long time to time out, since changing
the number back calls the _Change event. I should have written:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Range("A2:A300").Value = 1#
Range("A301:A500").Value = 2#
Range("A501:A700").Value = 3#
Application.EnableEvents = True
End If
End Sub