Solved: TextBox formula

[Barryj]

I want put a formula in a TextBox2 that should read:
IF(TextBox1<R1C13Sheet1,"A","B")
is this on the right track, I have not been able to get it to work?

[grichey]

I'm curious how you select a text box as well.

[Barryj]

What is happening is that a user puts a number in textbox1 and if that number is less than the number on sheet 1 cell M1 then the letter A appears in textbox2 if others wise the letter B appears.

Hope this make a little sense?

[Bob Phillips]

Textboxes are not worksheet objects, so you cannot test them in a formula, you have to use VBA.

[RonMcK]

Load this in your UserForm module:
[vba]Private Sub TextBox1_Change()

Load UserForm1
If Val(TextBox1.Value) < Cells(1, 1) Then ' I used A1 (1,1) for my use; change to M1 (13,1) for yours.
TextBox2.Value = "A"
Else
TextBox2.Value = "B"
End If

End Sub
[/vba] Something like this is what you need to use. Notice that you do not use the IF syntax of the spreadsheet but rather the Basic syntax (for VBA in this case). If you already ahve a TextBox1_Change() event, you'll need to add this to it.

Cheers,

[Barryj]

At the moment it is only putting either the A or the B no matter what the number in textbox1.

Its not working in the fact that if the number is less than M1 it should be A and if greater should be B.

Tried mucking around with it but not able to get it to work, any thoughts?

Must be close Ron as it is putting a letter in the right spot.

[RonMcK]

Barryj,

Hmm? Please help me here. What are you expecting that you are not seeing?

What value did you put in M1? What values when entered in Textbox1 yield an 'A' in Textbox2 and what values yield a 'B'? What did you get when Textbox1 = M1?

Thanks,

[Barryj]

Say the number 10 is in cell M1 then when 9 is entered into textbox1 the letter A will be put in textbox2, if the number in textbox1 is greater than M1 then the letter B is put in textbox2, if nothing is in textbox1 then nothing to be shown in textbox2.

Hope this makes more sense.

Thanks for the help Ron.

[RonMcK]

barryj,

So, when you enter each of those values in M1 and textbox1, what do you get in textbox2? I need to change the code for the blank M1 but before I do that please let me know what you are getting. In particular, what is happening wrong.

Thanks!

[Barryj]

It is only showing the A at the moment if I swap the <> around then it only shows the B, its not showing the B if the textbox1 number is greater than number in M1.

[RonMcK]

Barryj,

Give me a half hour or so, I have a meeting to attend.

[RonMcK]

barryj,

Try this:[VBA]Private Sub TextBox1_Change()

Dim testcell As Long
testcell = Cells(1, 13)
Debug.Print "before", Cells(1, 13).Value, ">" & TextBox1 & "<", ">" & TextBox2 & "<"
Load UserForm1 'Adjust User Form number as needed
' Change Cell(1, 13) reference if not using cell M1 for your test
If Str(testcell) = "" Then
TextBox2.Value = ""
Else
If Val(TextBox1) < testcell Then
TextBox2.Value = "A"
Else
TextBox2.Value = "B"
End If
End If
Debug.Print "after", Cells(1, 13).Value, ">" & TextBox1 & "<", ">" & TextBox2 & "<"
End Sub[/VBA]
Open the View > Immediate window and use F8 to step thru the code if you want. Remark out (or delete) the debug.print lines when this is working the way you want.

Thanks,

[Barryj]

Thankyou very much Ron that is working fine, I will mark this thread as solved.

[RonMcK]

Barryj,

You're very welcome. Glad to be able to assist you.

Cheers,