Can I do this in a formula, is VB needed?

[graybeam]

I'm setting up a form for people to enter information into. I have a free-form text field that users need to be able to type into, so I can't put a formula into it. However, I want a value to be "injected" into it upon a certain condition based on a formula from another cell "watching" that free-text cell.

I think I'm going to need to move to VB for this unless there's a canned function that inserts text to a given destination.

Thanks!

 

[repairman615]

Make sure this is the only code in there. I think you may have an earlier bit than:


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <br><SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, d <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> Arng <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("B3:B17"))<br>    <SPAN style="color:#00007F">Set</SPAN> Arng = Range("A3:A17")<br>    <br>    <br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> d <SPAN style="color:#00007F">In</SPAN> Range("B3:B17")<br>                <SPAN style="color:#00007F">If</SPAN> d.Value = "Encore:" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> d<br>            <br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A3:A17")<br>                <SPAN style="color:#00007F">If</SPAN> UCase(c.Value) = "EC." <SPAN style="color:#00007F">Then</SPAN><br>                    Target.Offset(-1, 0).Value = "Encore:"<br>                    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> c<br>            <br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[graybeam]

What version of Excel are you using? All I have in the VB window is the code from your last post and I'm still getting the pop-ups. Do you have somewhere you can post the spreadsheet you are editing and I can see if I'm crazy?

 

[repairman615]

I am using 2007

here is a link to the file:

http://www.4shared.com/file/EIz2vi59/Test_For_Encore.html

 

[Scott Huish]

Get rid of the Worksheet Change code and try this Worksheet Calculate code instead:

Private Sub Worksheet_Calculate()
Dim c As Range
Application.EnableEvents = False
For Each c In Range("A3:A17")
    If UCase(c) = "EC." Then c.Offset(, 1) = "Encore"
Next c
Application.EnableEvents = True
End Sub

 

[graybeam]

I am using 2007

here is a link to the file:

http://www.4shared.com/file/EIz2vi59/Test_For_Encore.html

Yes! It works! Thanks very much for all the help!!! That's so cool