Something else you may want to consider doing with my code. As posted, my
code will pop up the "The value in this cell cannot be changed!" message,
but leave the active cell at the location it moved to when the user hit
Enter or mouse clicked. Better (I think) would be to re-activate the cell
the user attempted to change so the message that popped up references what
the user is looking at when he/she dismisses the MessageBox. To accomplish
this, just add this line of code...

Target.Select

immediately *after* this line of code...

Target.Value = OldValue

--
Rick (MVP - Excel)


"YY san." wrote in message
...
Hi Rick,
Thanks for your help, it is exactly what I wanted. By the way, can the
cell
ID in the code change to a range instead of individual cell ID? I have
like
A1 ~ A50. Thanks,

To the rest of the folks,
thanks for your responses too! Totally agreed, we are all still learning.
I
have got great tips and solution after I hooked onto this forum!


"Rick Rothstein" wrote:

LOL... we all do Gord, not just you. I can't even begin to tell you how
much
I have learned from you, as well as the others here, since I started
volunteering in the Excel newsgroups. The newsgroups are an amazing
resource
for all... from novice users right up to the most experienced users.

As for the code... using "global" variables to set up a cooperative
exchange
of information between event procedures... I got quite good at
implementing
this type of coding back when I volunteered in the compiled VB
newsgroups. I
ended up developing several solutions to "it can't be done" type problems
back then using this technique... the technique can be quite powerful
(when
the situation is right for it).

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I just keep on learning<g


Gord

On Sun, 22 Feb 2009 13:34:46 -0500, "Rick Rothstein"
wrote:

You might be able to make use of this idea. Right click tab for the
worksheet that has the cells you want to "protect" and select View Code
from
the popup menu that appears, then copy/paste the following code into
the
code window that appeared (see my additional comments after the
code)...

'*************** START OF CODE ***************
Dim OldValue As Variant
Private Const ProtectedAddresses As String = "A1,B2,C3,D4"

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
MsgBox "The value in this cell cannot be changed!"
Target.Value = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then
OldValue = Target.Value
End If
End Sub
'*************** END OF CODE ***************

Change my example cell address (A1,B2,C3,D4) to the cell addresses of
the
cells you want to "protect" in the Private Const statement at the
beginning
of the code. Now, go back to the worksheet and try to change the values
in
those cells.