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?