Solved: How do I get the formulas to work on my userform

[Darren]

Hi Captains

I have created a sales tax calculation in excel. I have also created a Userform in the same workbook. My question is and i have searched all day is how do i get the formulas in sheet1 to work on my user form, I must stress i have no clue how to tie them together.

I must mention that i have made application hide True for now. On the final application i will make it false so that only the Userform displays when lauching the application.

1) I would like the user to enter turnover in the Box on the userform and the rest must automatically fill.

2) I would also like the user to enter the box " Cost of the items you Purchased with tax included and then the bottom two block to auto fill.

I hope this is possible


Thanking you for your help and patience


Darren
South Africa

Attached file is my work in progress

[Bob Phillips]

Private Sub TextBox1_Change()
TextBox2.Text = Format(Round(TextBox1.Text * 14 / 114, 2), "#,##0.00")
TextBox3.Text = Format(TextBox1.Text - TextBox2.Text, "#,##0.00")
End Sub


Private Sub TextBox4_Change()
TextBox5.Text = Format(Round(TextBox4.Text * 14 / 114, 2), "#,##0.00")
TextBox6.Text = Format(TextBox4.Text - TextBox5.Text, "#,##0.00")
End Sub

[Darren]

Hi XLD

I will try it now

do i insert in the sheet or the workbook XlD


Darren

[mdmackillop]

Hi Darren
It's best to give your controls meaningful names. The second solution looks the same as the first.
Regards
Malcolm
Private Sub TextBox1_AfterUpdate()
TextBox2 = TextBox1 * 14 / 114
TextBox3 = TextBox1 - TextBox2
End Sub

[mdmackillop]

Insert the code in the Userform code area.

[Darren]

Hi Malcolm and XLD

Thanks for this quick soloution to the problem and works like a charm.

I do find that if i enter the turnover and then use backspace to insert a different turnover in the intrest of testing i get "Run-time error '13': type mismatch... when i get to the last digit on backspace. How do i tell VBA to ignore the error and absorb/allow as many backspaces that are inputed by the user ?

Thanks guys for your superb knowledge and help

Darren
South Africa

[Darren]

working file

[Bob Phillips]

Private Sub TextBox1_Change()
With TextBox1
If .Text = "" Then
TextBox2.Text = ""
TextBox3.Text = ""
Else
TextBox2.Text = Format(Round(.Text * 14 / 114, 2), "#,##0.00")
TextBox3.Text = Format(.Text - TextBox2.Text, "#,##0.00")
End If
End With
End Sub


Private Sub TextBox4_Change()
With TextBox4
If .Text = "" Then
TextBox5.Text = ""
TextBox6.Text = ""
Else
TextBox5.Text = Format(Round(.Text * 14 / 114, 2), "#,##0.00")
TextBox6.Text = Format(.Text - TextBox5.Text, "#,##0.00")
End If
End With
End Sub


Do note Malcolm's point about meaningful names for the controls though, it makes life simpler.

Also, you might want to make the Enter event for TB2 and TB3 go stratight to TB4 etc., those boxes should not be editable.

[Darren]

Thank you XLD

will apply code and test

Kindest regards

Darren
South Africa

[mdmackillop]

Using the AfterUpdate method will recalculate only when you exit the textbox and should avoid the error message..

[Darren]

Thanks Xld and My Mentor Malcolm

It works what can i say

30 minutes to solve a problem

You both are the best
Darren
South Africa

[Bob Phillips]

True Malcolm, but seeing the other boxes update as you type is nice <g>

[Bob Phillips]

This is the sort of thing we mean.

PS Can I have a ride in your helicopter?

[Darren]

Xld i owe you

You can have as many rides as you please:beerchug: :bigdance2

[mdmackillop]

True Malcolm, but seeing the other boxes update as you type is nice <g> Ahhh! Added entertainment value. I never thought of that. :rotlaugh:
And I want to be there when you try to climb into that helicopter

[Bob Phillips]

I meant to show you an example of the thought on tabbing.

[Darren]

Hi XLD

Thanks for that now the numbers cannot be edited.

Thank you for sharing your expert knowledge


Kindest regards


Darren
South Africa