PDA

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



Darren
08-14-2007, 11:56 AM
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
08-14-2007, 12:11 PM
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
08-14-2007, 12:15 PM
Hi XLD

I will try it now

do i insert in the sheet or the workbook XlD


Darren

mdmackillop
08-14-2007, 12:29 PM
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
08-14-2007, 12:31 PM
Insert the code in the Userform code area.

Darren
08-14-2007, 12:51 PM
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
08-14-2007, 12:55 PM
working file

Bob Phillips
08-14-2007, 01:01 PM
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
08-14-2007, 01:05 PM
Thank you XLD

will apply code and test

Kindest regards

Darren
South Africa

mdmackillop
08-14-2007, 01:09 PM
Using the AfterUpdate method will recalculate only when you exit the textbox and should avoid the error message..

Darren
08-14-2007, 01:13 PM
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
08-14-2007, 01:13 PM
True Malcolm, but seeing the other boxes update as you type is nice <g>

Bob Phillips
08-14-2007, 01:15 PM
This is the sort of thing we mean.

PS Can I have a ride in your helicopter?

Darren
08-14-2007, 01:24 PM
Xld i owe you

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

mdmackillop
08-14-2007, 01:57 PM
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
08-14-2007, 11:57 PM
I meant to show you an example of the thought on tabbing.

Darren
08-15-2007, 04:21 AM
Hi XLD

Thanks for that now the numbers cannot be edited.

Thank you for sharing your expert knowledge


Kindest regards


Darren
South Africa