Re: Add text based on imput in another cell

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)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in
message news:OJ7iD4YrIHA.4280@xxxxxxxxxxxxxxxxxxxxxxx
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" <deerlake@xxxxxxxxxxxxx> wrote in message
news:EH7Tj.69417$Q52.24492@xxxxxxxxxxxxxxxxxxxxxxxxx
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)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote
in message news:%23k9sqT7qIHA.3408@xxxxxxxxxxxxxxxxxxxxxxx
Give this a try and see if it does what you want. Click the tab at the
bottom of the work*** 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 work*** 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" <deerlake@xxxxxxxxxxxxx> wrote in message
news:gOmSj.45806$%15.33243@xxxxxxxxxxxxxxxxxxxxxxxxx
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)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote
in message news:erlZLN6qIHA.4716@xxxxxxxxxxxxxxxxxxxxxxx
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" <deerlake@xxxxxxxxxxxxx> wrote in message
news:kGgSj.45664$%15.17750@xxxxxxxxxxxxxxxxxxxxxxxxx
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)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx>
wrote in message news:%23y4UxZ1qIHA.1736@xxxxxxxxxxxxxxxxxxxxxxx
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" <deerlake@xxxxxxxxxxxxx> wrote in message
news:2j9Sj.1669$_.1332@xxxxxxxxxxxxxxxxxxxxxxxxx
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 (C17>1980,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" <deerlake@xxxxxxxxxxxxx> wrote in message
news:rx2Sj.1452$_.1022@xxxxxxxxxxxxxxxxxxxxxxxxx
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

















.