I have a UserForm that adds and edits data on a worksheet. To load the
UserForm the user Double Clicks a cell that contains a reference number, the
macro searchs for the reference number in another worksheet and loads the
UserForm with the associated data. I have to keep the cell unlocked so the
user can double click it. The problem is that it is possible for the user to
delete the reference number. Is there a way to keep that cell from being
changed and still allow my Double Click Event to work?

Ryan,

Use two events. See code below, but change the address to the address or name of the cell that you
are using.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$B$5" Then MsgBox "You double-clicked B5"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$B$5")) Is Nothing Then Exit Sub
MsgBox "You can double-click B5 but you cannot edit it."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End Sub


"RyanH" wrote in message
...
I have a UserForm that adds and edits data on a worksheet. To load the
UserForm the user Double Clicks a cell that contains a reference number, the
macro searchs for the reference number in another worksheet and loads the
UserForm with the associated data. I have to keep the cell unlocked so the
user can double click it. The problem is that it is possible for the user to
delete the reference number. Is there a way to keep that cell from being
changed and still allow my Double Click Event to work?

On May 13, 11:03 pm, RyanH wrote:
I have a UserForm that adds and edits data on a worksheet. To load the
UserForm the user Double Clicks a cell that contains a reference number, the
macro searchs for the reference number in another worksheet and loads the
UserForm with the associated data. I have to keep the cell unlocked so the
user can double click it. The problem is that it is possible for the user to
delete the reference number. Is there a way to keep that cell from being
changed and still allow my Double Click Event to work?

I'm not sure if this helps, but I just had all cells locked and the
sheet protected with users allowed to Select locked cells and Select
unlocked cell as the only options (Excel 03).
When I ran this...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Application.DisplayAlerts = False
MsgBox Target.Value
End Sub

the msgbox showed the Target value then after I clicked OK the
protection alert was shown.

However, when I changed the code to...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Application.DisplayAlerts = False
MsgBox Target.Value
Target.Offset(1, 0).Select
End Sub

as before, the msgbox showed the Target value, but the protection
alert did not appear after I clicked OK.

Maybe you could do it that way with your cells locked and the sheet
protected.

Ken Johnson

It's a little unclear from your posting whether you want this functionality
for a single cell or for a column of cells. I'll assume a column (the value
of 6 in the three If-Then tests is for Column F) for the example code below
my signature (see comment afterwards for if you need this functionality for
a single cell only). Right-click the tab for the worksheet you want this
functionality on and then copy/paste all of the code below into the code
window that appears. If you have any existing BeforeDoubleClick,
Worksheet_Change and/or Worksheet_SelectionChange event code, you will need
to move it into the code below where I have noted it should go.

Here are a couple of things you need to know about how this code works.
First, the code won't stop the user from changing the value in the cell, but
it will warn the user his/her change is not allowed and then restore the
original value to the cell. Second, this code will prevent you from making
any changes to cells in Column F unless you set the LetProgramChangeValue
variable to True first (remember to set it back to False immediately after
assigning your new value to the cell).

Rick

*************** START OF CODE ***************
Dim OldValue As Variant
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Target.Value = OldValue
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then OldValue = Target.Value
'
' Your SelectionChange event code, if any, goes here
'
End Sub
*************** END OF CODE ***************

NOTE: If you want this functionality for a single cell only, change the two
occurences of Target.Column=6 to Target.Address="$F$3" where I'm assuming
$F$3 is the reference (it must be the absolute reference) to the cell you
want to not be changeable; and also change the MessageBox message
accordingly.



"RyanH" wrote in message
...
I have a UserForm that adds and edits data on a worksheet. To load the
UserForm the user Double Clicks a cell that contains a reference number,
the
macro searchs for the reference number in another worksheet and loads the
UserForm with the associated data. I have to keep the cell unlocked so
the
user can double click it. The problem is that it is possible for the user
to
delete the reference number. Is there a way to keep that cell from being
changed and still allow my Double Click Event to work?

Just saw Bernie's post and it reminded me I had forgotten about
Application.Undo. Using that simplifies the code a little bit. Here is
revised code to make use of it (everything I wrote about the way the code
functions still applies)...

Rick

*************** START OF CODE ***************
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Application.Undo
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub
*************** END OF CODE ***************



"Rick Rothstein (MVP - VB)" wrote in
message ...
It's a little unclear from your posting whether you want this
functionality for a single cell or for a column of cells. I'll assume a
column (the value of 6 in the three If-Then tests is for Column F) for the
example code below my signature (see comment afterwards for if you need
this functionality for a single cell only). Right-click the tab for the
worksheet you want this functionality on and then copy/paste all of the
code below into the code window that appears. If you have any existing
BeforeDoubleClick, Worksheet_Change and/or Worksheet_SelectionChange event
code, you will need to move it into the code below where I have noted it
should go.

Here are a couple of things you need to know about how this code works.
First, the code won't stop the user from changing the value in the cell,
but it will warn the user his/her change is not allowed and then restore
the original value to the cell. Second, this code will prevent you from
making any changes to cells in Column F unless you set the
LetProgramChangeValue variable to True first (remember to set it back to
False immediately after assigning your new value to the cell).

Rick

*************** START OF CODE ***************
Dim OldValue As Variant
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Target.Value = OldValue
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then OldValue = Target.Value
'
' Your SelectionChange event code, if any, goes here
'
End Sub
*************** END OF CODE ***************

NOTE: If you want this functionality for a single cell only, change the
two occurences of Target.Column=6 to Target.Address="$F$3" where I'm
assuming $F$3 is the reference (it must be the absolute reference) to the
cell you want to not be changeable; and also change the MessageBox message
accordingly.



"RyanH" wrote in message
...
I have a UserForm that adds and edits data on a worksheet. To load the
UserForm the user Double Clicks a cell that contains a reference number,
the
macro searchs for the reference number in another worksheet and loads the
UserForm with the associated data. I have to keep the cell unlocked so
the
user can double click it. The problem is that it is possible for the
user to
delete the reference number. Is there a way to keep that cell from being
changed and still allow my Double Click Event to work?

I modified your code a bit, but I got it to work great, thanks.

I have two questions though:
1.) Why do you have to disable the events property?

2.) Why does the Application.Undo produce an error?

Thanks,
Ryan

"Rick Rothstein (MVP - VB)" wrote:

Just saw Bernie's post and it reminded me I had forgotten about
Application.Undo. Using that simplifies the code a little bit. Here is
revised code to make use of it (everything I wrote about the way the code
functions still applies)...

Rick

*************** START OF CODE ***************
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Application.Undo
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub
*************** END OF CODE ***************



"Rick Rothstein (MVP - VB)" wrote in
message ...
It's a little unclear from your posting whether you want this
functionality for a single cell or for a column of cells. I'll assume a
column (the value of 6 in the three If-Then tests is for Column F) for the
example code below my signature (see comment afterwards for if you need
this functionality for a single cell only). Right-click the tab for the
worksheet you want this functionality on and then copy/paste all of the
code below into the code window that appears. If you have any existing
BeforeDoubleClick, Worksheet_Change and/or Worksheet_SelectionChange event
code, you will need to move it into the code below where I have noted it
should go.

Here are a couple of things you need to know about how this code works.
First, the code won't stop the user from changing the value in the cell,
but it will warn the user his/her change is not allowed and then restore
the original value to the cell. Second, this code will prevent you from
making any changes to cells in Column F unless you set the
LetProgramChangeValue variable to True first (remember to set it back to
False immediately after assigning your new value to the cell).

Rick

*************** START OF CODE ***************
Dim OldValue As Variant
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Target.Value = OldValue
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then OldValue = Target.Value
'
' Your SelectionChange event code, if any, goes here
'
End Sub
*************** END OF CODE ***************

NOTE: If you want this functionality for a single cell only, change the
two occurences of Target.Column=6 to Target.Address="$F$3" where I'm
assuming $F$3 is the reference (it must be the absolute reference) to the
cell you want to not be changeable; and also change the MessageBox message
accordingly.



"RyanH" wrote in message
...
I have a UserForm that adds and edits data on a worksheet. To load the
UserForm the user Double Clicks a cell that contains a reference number,
the
macro searchs for the reference number in another worksheet and loads the
UserForm with the associated data. I have to keep the cell unlocked so
the
user can double click it. The problem is that it is possible for the
user to
delete the reference number. Is there a way to keep that cell from being
changed and still allow my Double Click Event to work?

1) If you change the contents of the cell you are in within the Change
event, it will trigger another Change event for that cell. Sometimes code
can slough this off without any noticeable problems; but, more often than
not, chaining the Change events in this way causes problems. If is simply
safer to shield the Change event from any changes you make within the Change
event itself.

2) It appears that if there is nothing to undo, the Undo method generates an
error. You can control that by doing this...

On Error Resume Next
Application.Undo
On Error GoTo 0

or, if you have an existing error handler active, just incorporate the
handling of this error within it.

Rick


"RyanH" wrote in message
...
I modified your code a bit, but I got it to work great, thanks.

I have two questions though:
1.) Why do you have to disable the events property?

2.) Why does the Application.Undo produce an error?

Thanks,
Ryan

"Rick Rothstein (MVP - VB)" wrote:

Just saw Bernie's post and it reminded me I had forgotten about
Application.Undo. Using that simplifies the code a little bit. Here is
revised code to make use of it (everything I wrote about the way the code
functions still applies)...

Rick

*************** START OF CODE ***************
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Application.Undo
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub
*************** END OF CODE ***************



"Rick Rothstein (MVP - VB)" wrote
in
message ...
It's a little unclear from your posting whether you want this
functionality for a single cell or for a column of cells. I'll assume a
column (the value of 6 in the three If-Then tests is for Column F) for
the
example code below my signature (see comment afterwards for if you need
this functionality for a single cell only). Right-click the tab for the
worksheet you want this functionality on and then copy/paste all of the
code below into the code window that appears. If you have any existing
BeforeDoubleClick, Worksheet_Change and/or Worksheet_SelectionChange
event
code, you will need to move it into the code below where I have noted
it
should go.

Here are a couple of things you need to know about how this code works.
First, the code won't stop the user from changing the value in the
cell,
but it will warn the user his/her change is not allowed and then
restore
the original value to the cell. Second, this code will prevent you from
making any changes to cells in Column F unless you set the
LetProgramChangeValue variable to True first (remember to set it back
to
False immediately after assigning your new value to the cell).

Rick

*************** START OF CODE ***************
Dim OldValue As Variant
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Target.Value = OldValue
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then OldValue = Target.Value
'
' Your SelectionChange event code, if any, goes here
'
End Sub
*************** END OF CODE ***************

NOTE: If you want this functionality for a single cell only, change the
two occurences of Target.Column=6 to Target.Address="$F$3" where I'm
assuming $F$3 is the reference (it must be the absolute reference) to
the
cell you want to not be changeable; and also change the MessageBox
message
accordingly.



"RyanH" wrote in message
...
I have a UserForm that adds and edits data on a worksheet. To load the
UserForm the user Double Clicks a cell that contains a reference
number,
the
macro searchs for the reference number in another worksheet and loads
the
UserForm with the associated data. I have to keep the cell unlocked
so
the
user can double click it. The problem is that it is possible for the
user to
delete the reference number. Is there a way to keep that cell from
being
changed and still allow my Double Click Event to work?

That makes total since. You are the man. I am slowly getting better at VBA,
I've been working with it now for 8 months. I have a two books, Excel VBA
Programming for Dummies by John Walkenbach and VBA and Macros for Excel by
Bill Jelen "Mr. Excel".

Do you recommend anyother good books?

Thanks,
Ryan

"Rick Rothstein (MVP - VB)" wrote:

1) If you change the contents of the cell you are in within the Change
event, it will trigger another Change event for that cell. Sometimes code
can slough this off without any noticeable problems; but, more often than
not, chaining the Change events in this way causes problems. If is simply
safer to shield the Change event from any changes you make within the Change
event itself.

2) It appears that if there is nothing to undo, the Undo method generates an
error. You can control that by doing this...

On Error Resume Next
Application.Undo
On Error GoTo 0

or, if you have an existing error handler active, just incorporate the
handling of this error within it.

Rick


"RyanH" wrote in message
...
I modified your code a bit, but I got it to work great, thanks.

I have two questions though:
1.) Why do you have to disable the events property?

2.) Why does the Application.Undo produce an error?

Thanks,
Ryan

"Rick Rothstein (MVP - VB)" wrote:

Just saw Bernie's post and it reminded me I had forgotten about
Application.Undo. Using that simplifies the code a little bit. Here is
revised code to make use of it (everything I wrote about the way the code
functions still applies)...

Rick

*************** START OF CODE ***************
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Application.Undo
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub
*************** END OF CODE ***************



"Rick Rothstein (MVP - VB)" wrote
in
message ...
It's a little unclear from your posting whether you want this
functionality for a single cell or for a column of cells. I'll assume a
column (the value of 6 in the three If-Then tests is for Column F) for
the
example code below my signature (see comment afterwards for if you need
this functionality for a single cell only). Right-click the tab for the
worksheet you want this functionality on and then copy/paste all of the
code below into the code window that appears. If you have any existing
BeforeDoubleClick, Worksheet_Change and/or Worksheet_SelectionChange
event
code, you will need to move it into the code below where I have noted
it
should go.

Here are a couple of things you need to know about how this code works.
First, the code won't stop the user from changing the value in the
cell,
but it will warn the user his/her change is not allowed and then
restore
the original value to the cell. Second, this code will prevent you from
making any changes to cells in Column F unless you set the
LetProgramChangeValue variable to True first (remember to set it back
to
False immediately after assigning your new value to the cell).

Rick

*************** START OF CODE ***************
Dim OldValue As Variant
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Target.Value = OldValue
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then OldValue = Target.Value
'
' Your SelectionChange event code, if any, goes here
'
End Sub
*************** END OF CODE ***************

NOTE: If you want this functionality for a single cell only, change the
two occurences of Target.Column=6 to Target.Address="$F$3" where I'm
assuming $F$3 is the reference (it must be the absolute reference) to
the
cell you want to not be changeable; and also change the MessageBox
message
accordingly.



"RyanH" wrote in message
...
I have a UserForm that adds and edits data on a worksheet. To load the
UserForm the user Double Clicks a cell that contains a reference
number,
the
macro searchs for the reference number in another worksheet and loads
the
UserForm with the associated data. I have to keep the cell unlocked
so
the
user can double click it. The problem is that it is possible for the
user to
delete the reference number. Is there a way to keep that cell from
being
changed and still allow my Double Click Event to work?

Actually, I have only been programming in VBA for a little more than a year,
myself; however, I have a 26+ year background in programming with more than
10 of them in the compiled version of VB, so my only real hurtle was in
learning the specific object models available in Excel (something I am still
doing to this day). That was sort of a long-winded way of saying I don't
think I know enough to give you a recommendation on a VBA book... sorry.

Rick


"RyanH" wrote in message
...
That makes total since. You are the man. I am slowly getting better at
VBA,
I've been working with it now for 8 months. I have a two books, Excel VBA
Programming for Dummies by John Walkenbach and VBA and Macros for Excel by
Bill Jelen "Mr. Excel".

Do you recommend anyother good books?

Thanks,
Ryan

"Rick Rothstein (MVP - VB)" wrote:

1) If you change the contents of the cell you are in within the Change
event, it will trigger another Change event for that cell. Sometimes code
can slough this off without any noticeable problems; but, more often than
not, chaining the Change events in this way causes problems. If is simply
safer to shield the Change event from any changes you make within the
Change
event itself.

2) It appears that if there is nothing to undo, the Undo method generates
an
error. You can control that by doing this...

On Error Resume Next
Application.Undo
On Error GoTo 0

or, if you have an existing error handler active, just incorporate the
handling of this error within it.

Rick


"RyanH" wrote in message
...
I modified your code a bit, but I got it to work great, thanks.

I have two questions though:
1.) Why do you have to disable the events property?

2.) Why does the Application.Undo produce an error?

Thanks,
Ryan

"Rick Rothstein (MVP - VB)" wrote:

Just saw Bernie's post and it reminded me I had forgotten about
Application.Undo. Using that simplifies the code a little bit. Here is
revised code to make use of it (everything I wrote about the way the
code
functions still applies)...

Rick

*************** START OF CODE ***************
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Application.Undo
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub
*************** END OF CODE ***************



"Rick Rothstein (MVP - VB)"
wrote
in
message ...
It's a little unclear from your posting whether you want this
functionality for a single cell or for a column of cells. I'll
assume a
column (the value of 6 in the three If-Then tests is for Column F)
for
the
example code below my signature (see comment afterwards for if you
need
this functionality for a single cell only). Right-click the tab for
the
worksheet you want this functionality on and then copy/paste all of
the
code below into the code window that appears. If you have any
existing
BeforeDoubleClick, Worksheet_Change and/or Worksheet_SelectionChange
event
code, you will need to move it into the code below where I have
noted
it
should go.

Here are a couple of things you need to know about how this code
works.
First, the code won't stop the user from changing the value in the
cell,
but it will warn the user his/her change is not allowed and then
restore
the original value to the cell. Second, this code will prevent you
from
making any changes to cells in Column F unless you set the
LetProgramChangeValue variable to True first (remember to set it
back
to
False immediately after assigning your new value to the cell).

Rick

*************** START OF CODE ***************
Dim OldValue As Variant
Dim LetProgramChangeValue As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Column = 6 Then Cancel = True
'
' Your BeforeDoubleClick event code, if any, goes here
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LetProgramChangeValue And Target.Column = 6 Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Target.Value = OldValue
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then OldValue = Target.Value
'
' Your SelectionChange event code, if any, goes here
'
End Sub
*************** END OF CODE ***************

NOTE: If you want this functionality for a single cell only, change
the
two occurences of Target.Column=6 to Target.Address="$F$3" where I'm
assuming $F$3 is the reference (it must be the absolute reference)
to
the
cell you want to not be changeable; and also change the MessageBox
message
accordingly.



"RyanH" wrote in message
...
I have a UserForm that adds and edits data on a worksheet. To load
the
UserForm the user Double Clicks a cell that contains a reference
number,
the
macro searchs for the reference number in another worksheet and
loads
the
UserForm with the associated data. I have to keep the cell
unlocked
so
the
user can double click it. The problem is that it is possible for
the
user to
delete the reference number. Is there a way to keep that cell from
being
changed and still allow my Double Click Event to work?