Add text based on imput in another cell
 

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

Add text based on imput in another cell
 

You posted in the Excel Programming section, but I don't think you need a
macro to do what you described. Put this function in an appropriate cell:
=IF(C1515,B21&C21,"")

Regards,
Ryan---

--
RyGuy


"HH" wrote:

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

Add text based on imput in another cell
 

Let say tht B21 originally contained:
=B19+B20
change this to:
=IF(C15=1,(B19+B20) & " and C15 has a one",B19+B20)
--
Gary''s Student - gsnu200782


"HH" wrote:

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

Add text based on imput in another cell
 

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

Add text based on imput in another cell
 

If you don't mind updating the whole worksheet as a batch, you can create a
button and then use something similar to the following code to loop thru all
cells:

Private Sub CommandButton1_Click()
Dim oRange As Range
Dim sTest As String
Dim iCount As Integer
Dim i As Integer

With Sheets("Sheet1")
Debug.Print .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If .Range("A" & i).Value < "" Then
If .Range("A" & i).Value < 1870 Then
.Range("B" & i).Value = .Range("B" & i).Value & " - may contain
lead"
End If
End If
Next
End With
End Sub

You may have to change the columns above as I am just using A & B for example.



"HH" wrote:

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

Add text based on imput in another cell
 

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

Add text based on imput in another cell
 

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

Add text based on imput in another cell
 

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

Add text based on imput in another cell
 

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

Add text based on imput in another cell
 

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

Add text based on imput in another cell
 

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

Add text based on imput in another cell
 

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

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)" 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

Add text based on imput in another cell
 

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

Add text based on imput in another cell
 

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