PDA

View Full Version : Textbox Userform: Autopopulate textbox from another textbox



jenroyce
07-05-2011, 08:50 PM
Hi,

Ive been trying to crack this project but to no avail.

I need to have 2 textboxes autopopulate on button click or after change event from 2 other textboxes' conditions.

here are the conditions:
1. If Tax Advance = 0; put Escrow Advance in Misc Amt field

2. If Tax Advance < Escrow Advance; put Tax Advance in Tax Arrears and the Difference (Escrow Advance minus Tax Advance) in Misc Amt

3. If Tax Advance > Escrow Advance; put Escrow Advance in Tax Arrears

4. If Tax Advance = Escrow Advance; put Tax Advance in Tax Arrears

NOTE:
* All figures should be in dollar currency

I have attached what i have so far. I hope someone can assist me.

Rayman
07-06-2011, 02:56 AM
Hi,

Ive been trying to crack this project but to no avail.

I need to have 2 textboxes autopopulate on button click or after change event from 2 other textboxes' conditions.

here are the conditions:
1. If Tax Advance = 0; put Escrow Advance in Misc Amt field

2. If Tax Advance < Escrow Advance; put Tax Advance in Tax Arrears and the Difference (Escrow Advance minus Tax Advance) in Misc Amt

3. If Tax Advance > Escrow Advance; put Escrow Advance in Tax Arrears

4. If Tax Advance = Escrow Advance; put Tax Advance in Tax Arrears

NOTE:
* All figures should be in dollar currency

I have attached what i have so far. I hope someone can assist me.

Tell us the password of your code if you want help!

jenroyce
07-06-2011, 03:01 AM
oooopss :blush my bad. i have re-attached the file to remove the password.

jenroyce
07-06-2011, 03:11 AM
I deleted the code i created in that file but here's what i entered.
it seems like when Textbox2 is less than textbox1 it doesnt give a value to textbox4(which is textbox1 minues textbox2) when the figure of textbox1 is say $30,000 and Textbox2 is say $500.00

Private Sub TextBox3_Change()
If TextBox1.Value = "" Then Exit Sub
If TextBox2.Value < TextBox1.Value Then TextBox3.Value = Format(TextBox2.Value, "$##,###,###.00")
If Format(TextBox2.Value, "$##,###,###.00") < Format(TextBox1.Value, "$##,###,###.00") Then TextBox3.Value = Format(TextBox1.Value, "$##,###,###.00")
If TextBox2.Value = TextBox1.Value Then TextBox3.Value = Format(TextBox2.Value, "$##,###,###.00")
End Sub
Private Sub TextBox4_Change()
If TextBox1.Value = "" Then Exit Sub
If TextBox2.Value = "0" Then TextBox4.Value = TextBox1.Value
If Format(TextBox2.Value, "$##,###,###.00") < Format(TextBox1.Value, "$##,###,###.00") Then TextBox4.Value = Format(CDbl(TextBox1.Value) - CDbl(TextBox2.Value), "$##,###,###.00")
If Format(TextBox1.Value, "$##,###,###.00") < Format(TextBox2.Value, "$##,###,###.00") Then TextBox4.Value = Format(TextBox4.Value, "$.00")
If Format(TextBox2.Value, "$##,###,###.00") > Format(TextBox1.Value, "$##,###,###.00") Then TextBox4.Value = Format(TextBox4.Value, "$.00")
If TextBox2.Value = TextBox1.Value Then TextBox4.Value = Format(TextBox4.Value, "$.00")
If TextBox4.Value < "0" Then TextBox4.Value = "$.00"
End Sub

jenroyce
07-06-2011, 10:01 AM
I'm having trouble with this code: If Format(TextBox2.Value, "$##,###,###.00") < Format(TextBox1.Value, "$##,###,###.00") Then TextBox4.Value = Format(CDbl(TextBox1.Value) - CDbl(TextBox2.Value), "$##,###,###.00")

When I enter 30,000 to textbox1 and enter 500 to textbox2, I don't get a result in textbox 4 which is textbox1 minus textbox2 if textbox1 is greater than textbox 2

mikerickson
07-06-2011, 10:35 AM
The reason that you don't get anything is that you are using string comparison and "$30,000.00" < "$500.00".

Try Val(Replace(TextBox2.Text,"$","")) < Val(Replace(TextBox1.Text,"$","")) Then...

jenroyce
07-06-2011, 06:33 PM
I changed the code to this: Private Sub CommandButton4_Click()
If Val(Replace(TextBox2.Text, "$", "")) = 0 Then TextBox4.Value = Val(Replace(TextBox1.Text, "$", ""))
If Val(Replace(TextBox2.Text, "$", "")) < Val(Replace(TextBox1.Text, "$", "")) Then
TextBox3.Value = TextBox2.Value
End If
If Val(Replace(TextBox2.Text, "$", "")) < Val(Replace(TextBox1.Text, "$", "")) Then
TextBox4.Value = CDbl(TextBox1.Value) - CDbl(TextBox2.Value)
If Val(Replace(TextBox2.Text, "$", "")) > Val(Replace(TextBox1.Text, "$", "")) Then TextBox3.Value = TextBox1.Value
End If
End Sub

I still can run the program properly with the following conditions

1. if textbox2(tax advance) is blank or zero, textbox1(escrow advance) value should be on textbox4(misc amount)

2. if textbox2(tax advance) is less than textbox1(escrow advance), textbox2(tax advance) should be on textbox3(tax arrears) and the difference of textbox1(escrow advance) and textbox2(tax advance) should be on textbox4(misc amount)

3. if textbox2(tax advance) is greater than textbox1(escrow advance), then textbox1(escrow advance) value should be in textbox3(tax arrears)

4. if textbox2(tax advance) is equal to textbox1(escrow advance), then textbox2(tax advance) should be in textbox3(tax arrears)

jenroyce
07-11-2011, 08:21 PM
Still can't figure this one out :( help:confused4