Locking Cells - Unconditionally, No "Protect"?

P

(PeteCresswell)

Is there any way to make cells totally, completely unchangeable without invoking
the "Protect Sheet" functionality?

I'm creating an Excel sheet that will be used as a data entry template and one
of the columns contains record ID numbers from a database which, if changed by
the user, will break the application that has to read the sheet.

Or should I just bite the bullet and use "Protect Sheet"? My problem with that
is that it seems like a determined user could unprotect it and....
 
D

Dave Peterson

I don't think so.

I would guess that any solution would rely on macros and macros could be
disabled.
 
T

Tom

I agree with Dave.

However, if you can be sure the macros won't be disabled, you could
write a simple macro to detect if the cell's value has been changed
(assuming it's cell A1):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox ("That was a mistake!")
ActiveWorkbook.Close
End If
End Sub

Of course, you could take less drastic action than closing the file.
Or you could even take preemptive action:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox ("Don't even think about changing that cell!")
ActiveCell.Offset(1, 1).Activate
End If
End Sub

But as Dave points out, if the user disables macro execution, this
method fails.

--Tom.
 
S

Suresh

Try this
On the field which you want to block from users manipulating - Put a
data validation as follows:

Data>Validation>Validation criteria>Allow>Custom>Formula <0,>0

Set the error message to Stop so that it will not allow any changes!

However, this is to prevent accidental changes only.
I agree with Dave.

However, if you can be sure the macros won't be disabled, you could
write a simple macro to detect if the cell's value has been changed
(assuming it's cell A1):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox ("That was a mistake!")
ActiveWorkbook.Close
End If
End Sub

Of course, you could take less drastic action than closing the file.
Or you could even take preemptive action:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox ("Don't even think about changing that cell!")
ActiveCell.Offset(1, 1).Activate
End If
End Sub

But as Dave points out, if the user disables macro execution, this
method fails.

--Tom.
Click to expand...
 
Top