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]

That would be a neat function, but I am 99% sure VBA will be your ticket.

Maybe a worksheet change event would help. If you would like post the details like cell locations and other info needed, a solution could most likely be provided.

 

[graybeam]

Details, ok here we go:

I'm making a set-list generator for my band with song numbers on the left and a validation list of songs on the right. If I start in the first song slot (B3) and populate a song from the validation list a "1." will populate cell (A3). If I populate cell (B4) with the next song, (A4) will populate with "2.". However if I skip (B5) and populate a song in (B6) the numbering stops and (A6) is populated with "ec." for encore. It's not terribly evident that "ec." means encore, so I thought to make the formula put the word "Encore:" into cell (B5) as a sort of heading for the following encore songs.

I have a pretty crazy little formula in the numbering field already, so it will be interesting to add another level of complexity to it. As a side note, is there a way to take multiple actions on an IF statement if a certain condition is true? That's what I'd have to do I think.

Hope that makes a little sense.

 

[repairman615]

Right click on your sheet tab, select veiw code. Paste in the below and then close the Editor. Now make some changes that will cause the "ec" to show in column A:A.

Be sure to test on a copy.

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> Changed <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("A1:A500"))<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>            <br>            <SPAN style="color:#00007F">If</SPAN> Target.Value = "EC" <SPAN style="color:#00007F">Or</SPAN> Target.Value = "ec" <SPAN style="color:#00007F">Then</SPAN><br>                Target.Offset(-1, 1).Value = "Encore"<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">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Scott Huish]

BTW if you use UCASE, you only have to make one comparison:

Instead of:

If Target.Value = "EC" Or Target.Value = "ec"

If UCase(Target.Value)="EC" Then ...

 

[Scott Huish]

You also don't want to check from row 1, because you can't offset -1 rows from there:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set c = Intersect(Target, Range("A2:A500"))
If c Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each d In c
        If UCase(d) = "EC" Then d.Offset(-1, 1) = "Encore"
    Next
Application.EnableEvents = True
End Sub

 

[repairman615]

Thanks HOTPEPPER,

I have never used Ucase, I have seen the term before though. Also I see now that offset would have an error there. I am glad you pointed this out, I am trying out the Ucase now, Very cool!!

 

[graybeam]

Hmm, I've tried using the formulas posted but I can't seem to get it to work. I made a couple modifications on the range so it's only looking at the 15 cells that would have "ec." in them and added a period to the "EC." in the UCase statement and then just a colon to the word "Encore:"

I'm not sure what event this is set to trigger on. It should probably be set to fire off when something in B3:B17 is changed or when cells are populated in that range.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set c = Intersect(Target, Range("A3:A17"))
If c Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each d In c
        If UCase(d) = "EC." Then d.Offset(-1, 1) = "Encore:"
    Next
Application.EnableEvents = True
End Sub

 

[repairman615]

Try:

<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">Set</SPAN> c = Intersect(Target, Range("B3:B17"))<br><br><SPAN style="color:#00007F">If</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> d <SPAN style="color:#00007F">In</SPAN> c<br>        <SPAN style="color:#00007F">If</SPAN> UCase(Target.Value) = "EC." <SPAN style="color:#00007F">Then</SPAN> Target.Offset(-1, 1) = "Encore:"<br>    <SPAN style="color:#00007F">Next</SPAN><br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[graybeam]

Maybe I'm inserting the code wrong. I'm going to the developer tab, opening Visual Basic, right clicking on the project then clicking "Insert" > "Module" and pasting it into the code window then testing it. Do I need to take another step, or am I going about it entirely wrong? I'm kind of green when it comes to the Excel VBA.