Keep Cell Unlocked but Don't Allow User to Change the Text in Cell

[RyanH]

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?

 

[Bernie Deitrick]

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

 

[Ken Johnson]

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

 

[Rick Rothstein \(MVP - VB\)]

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.

 

[Rick Rothstein \(MVP - VB\)]

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 ***************

 

[RyanH]

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\)]

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]

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\)]

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