Rick,
That makes sense when I think about it. The code works great now.
Thanks for your hard work and patients.
Hank
"Rick Rothstein (MVP - VB)" wrote in
message ...
I wrote the code making the warning disappear while the user is entering
something in C21 on purpose. The problem as I saw it was, if the warning
were allowed to remain during editing, there would be no practical way to
protect if from being modified (either on purpose or by accident). Consider
if the user accidentally deleted the closing parenthesis from the warning
message (although any deletion and/or modification will have the same
effect, I just chose the smallest one)... then the program would not be
able to recognize it as the warning message any more and would treat it as
newly edited text. That means when the user leaves the cell, a new warning
message would be added to the remaining text in the cell (the user's new
text plus the remainder of the modified warning message). To protect the
warning message against this scenario, I decided to remove it when the user
went to edit the text in C21 and restore it when they were finished. If you
really do not like this action, I can modify the code to leave the warning
message in the cell during editing... just let me know. Meanwhile, here is
the code to place the warning message in front of the user's entry...

'********** START OF CODE **********
Const Warning As String = "(Construction material may contain lead.) "

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$17" Or Target.Address = "$C$21" Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
If Range("C17").Value < 1980 Then
Range("C21").Value = Warning & Range("C21").Value
End If
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Value
If Target.Address = "$C$21" Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
Application.EnableEvents = True
Else
If Range("C17").Value < 1980 And _
InStr(Range("C21").Value, Warning) = 0 Then
Application.EnableEvents = False
Range("C21").Value = Warning & Range("C21").Value
Application.EnableEvents = True
End If
End If
End Sub
'********** END OF CODE **********

Rick


"HH" wrote in message
. ..
Rick,
That works great except when I try to manually type something else in
C21. Then the warring is not visible. The new typed text is added before
the warning. After I move to another cell the warning reappears along
with the manually entered text.
It will work OK as is but would be better if the warning was visible and
new text was added after the warning.
Hank
"Rick Rothstein (MVP - VB)" wrote
in message ...
I had to guess at the functionality you would have wanted if C21 was
empty and it looks like I guessed incorrectly. Try replacing **all** the
code I gave you earlier with the code below and see if it functions like
you want...

'********** START OF CODE **********
Const Warning As String = " (Construction material may contain lead.)"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$17" Or Target.Address = "$C$21" Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
If Range("C17").Value < 1980 Then
Range("C21").Value = Range("C21").Value & Warning
End If
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Value
If Target.Address = "$C$21" Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
Application.EnableEvents = True
Else
If Range("C17").Value < 1980 And _
InStr(Range("C21").Value, Warning) = 0 Then
Application.EnableEvents = False
Range("C21").Value = Range("C21").Value & Warning
Application.EnableEvents = True
End If
End If
End Sub
'********** END OF CODE **********

Rick



"HH" wrote in message
. ..
Rick,
The code works - but only if there is another entry in C21. If the C21
does not have an entry when the date is entered into C17 it does not
work.
The warning will appear in C21 after any entry is added - even a space.
Also - if I enter a wrong date (pre 1980) it will enter the warning.
If I then enter the correct date (still pre 1980) it enters the warning
again. i.e. If I enter 1950 the warning appears. If I change the date
to 1955 the warning appears again.. If I change the date to after
1980 - it work great- all warnings are removed.

Hank

"Rick Rothstein (MVP - VB)" wrote
in message ...
Give this a try and see if it does what you want. Click the tab at the
bottom of the worksheet you want this functionality on and select
"View Code" from the popup menu that appears; then Copy/Paste the code
below into the code window that appeared when you did that. Now, go
back to the worksheet and enter different combinations of dates in C17
and text in C21 to see if the code is doing what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Const Warning As String = " (Construction material may contain
lead.)"
If Target.Address = "$C$21" And Len(Target.Value) 0 Then
If Range("C17").Value < 1980 Then
Application.EnableEvents = False
Target.Value = Target.Value & Warning
Application.EnableEvents = True
End If
ElseIf Target.Address = "$C$17" And Len(Range("C21").Value) 0 Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
If Target.Value < 1980 Then
Range("C21").Value = Range("C21").Value & Warning
End If
Application.EnableEvents = True
End If
End Sub

Rick


"HH" wrote in message
. ..
Rick,
Yes, C17 and C21 are the only cells involved. What I want to do is
add the staement "Construction material may contain lead." to C21 if
a year less than 1980 is entered in C17.
You are right, there may be other text already in the cell when the
statement is to be added. Also there may be text added to C21 after
the statement is added.
"Rick Rothstein (MVP - VB)"
wrote in message ...
If the user can type into the cell, then you cannot put a formula in
the cell also (it will be overwritten by the user's entry). The only
way to do what you want is through an event procedure. In order to
give you the code you will need, we need some more information. Are
you interested in cells C17 and C21 only? Or is this a functionality
you need across multiple columns? If multiple columns, which ones
(start column, end column)? Always rows 17 and 21, or do other rows
need to react to the value typed into C17?

Rick


"HH" wrote in message
. ..
Rick,
C21 is a general text cell where the user can type comments.
There is no formula in this cell now.

"Rick Rothstein (MVP - VB)"
wrote in message ...
What is in C21 now? By that I mean, does it contain a formula
which displays text (if so, tell us the formula) or does it
contain text typed in by the user?

Rick


"HH" wrote in message
...
Maybe I was not clear enough - or maybe I don't understand.
I'll try again..

If I put a value less than 1980 in cell C17, I would like a text
statement added to cell C21. This added text statement would say
"Construction material may contain lead." There may already be a
text statement in C21 so the new statement would be added at the
end of whatever is already in the cell.
What I have come up with is: =If (C171980,C21="Construction
material may contain lead.","") I think this would delete
whatever is already in C21 and replace the Consturction
material...statement. But even if it would work - I don't know
where to add the =if statement.
Thanks

"HH" wrote in message
...
Based on value in C15 I want to have a text statement added to
cell B21. I would like this to be put at the end of whatever is
already in B21 - not just replace the cell content.

Need help with the code - and where it would go.

Thanks