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?