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]

This will go into the sheet code.


Right click the worksheet tab, veiw code, then paste in there.

Or

Dbl click the sheet that you would like this to apply to in the project window

 

[graybeam]

Still no love, darn it. Here's a screen shot of the interface.

This is how it looks:
http://i46.photobucket.com/albums/f120/NothingSaid/Howitlooks.jpg


This is what I'd like it to do when I skip a line:
http://i46.photobucket.com/albums/f120/NothingSaid/HowIwantittolook.jpg

 

[repairman615]

How is the ec. placed into the sheet? This might throw that off, I am not sure...

sheet code:

<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<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 = Intersect(Target, Range("A3:A17"))<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>        <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><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[graybeam]

Still no cigar. Dang it, I'm losing hope >.<

The ec. is input by this formula (I had to use HTML or it wouldn't post correctly):

=IF(ISBLANK(OFFSET(A7,0,1)),"",IF(OR(OFFSET(A7,-1,0)="ec.",AND(ISBLANK(OFFSET(A7,-1,1)),ISTEXT(B7))),"ec.",IF(B7="<select song>","",IF(ISBLANK(B7),"","5."))))

 

[repairman615]

Here, try this and report any msgbox numbers that pop up (if any).


<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<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 = Intersect(Target, Range("A3:A17"))<br>    MsgBox "#1...shows macro fires"<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>        MsgBox "#2...passed a test"<br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A3:A17")<br>                <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>            MsgBox "#3...end loop"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[graybeam]

No pop ups :-S

I'll post you a temporary link to the spreadsheet, that way you can play with it. I think that's probably the best solution

http://dl.dropbox.com/u/1532402/X3%26fT4.zip

 

[repairman615]

Don't give up. I am seeing some results. Here is what I am doing.


Right click the sheet tab at the bottom, select veiw code, then paste this into the window that pops up. You will need to copy from private sub....down to end sub. Close that window and now the macro should work when you change any cell in range b3:b17

<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><br><br></FONT>

 

[graybeam]

Ok now I feel dumb. There's this little drop down menu that pops up and asks if you want to enable macros (that I didn't see)... so now I'm seeing results, but I'm getting a lot of pop up windows whenever I add anything.

Sorry for the "doh!" move >.<

 

[repairman615]

What are the pop up displaying?

 

[graybeam]

The song title, then about 8 or 10 more pop ups with no text.