Solved: Value Added Tax conversion problem
Hi Captains
I have a spread sheet with the folowing Formula =C25/(1+H27/100)
This in effect means what ever value is entered in H27 is the Tax Rate, it is currently set to 14.
I have coded my userform as follows
[VBA]Private Sub TextBox1_AfterUpdate()
TextBox1.Text = Format(TextBox1.Text, "#,##0.00")
If TextBox1.Text <> "" Then
End If
TextBox2.Text = Format(TextBox1.Text / 114 * 100, "#,##0.00")
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub
Private Sub TextBox2_AfterUpdate()
TextBox2.Text = Format(TextBox2.Text, "#,##0.00")
If TextBox2.Text <> "" Then
End If
TextBox4 = SumOne1
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub
Private Sub TextBox3_AfterUpdate()
TextBox3.Text = Format(TextBox3.Text, "#,##0.00")
If TextBox3.Text <> "" Then
End If
TextBox4 = SumOne1
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub
Private Sub TextBox4_AfterUpdate()
TextBox4.Text = Format(TextBox4.Text, "#,##0.00")
If TextBox4.Text <> "" Then
End If
End Sub
Private Sub TextBox5_AfterUpdate()
TextBox5.Text = Format(TextBox5.Text, "#,##0.00")
If TextBox5.Text <> "" Then
End If
TextBox6.Text = Format(TextBox5.Text / 114 * 100, "#,##0.00")
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub
Private Sub TextBox6_AfterUpdate()
TextBox6.Text = Format(TextBox6.Text, "#,##0.00")
If TextBox6.Text <> "" Then
End If
TextBox8 = SumTwo1
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub
Private Sub TextBox7_AfterUpdate()
TextBox7.Text = Format(TextBox7.Text, "#,##0.00")
If TextBox7.Text <> "" Then
End If
TextBox8 = SumTwo1
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub
Private Sub TextBox8_AfterUpdate()
TextBox8.Text = Format(TextBox8.Text, "#,##0.00")
If TextBox8.Text <> "" Then
End If
End Sub
Private Sub TextBox9_AfterUpdate()
TextBox9.Text = Format(TextBox9.Text, "#,##0.00")
If TextBox9.Text <> "" Then
End If
TextBox10.Text = Format(TextBox9.Text / 114 * 100, "#,##0.00")
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub
Private Sub TextBox10_AfterUpdate()
TextBox10.Text = Format(TextBox10.Text, "#,##0.00")
If TextBox10.Text <> "" Then
End If
TextBox12 = SumThree1
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub
Private Sub TextBox11_AfterUpdate()
TextBox11.Text = Format(TextBox11.Text, "#,##0.00")
If TextBox11.Text <> "" Then
End If
TextBox12 = SumThree1
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub
Private Sub TextBox12_AfterUpdate()
TextBox12.Text = Format(TextBox12.Text, "#,##0.00")
If TextBox12.Text <> "" Then
End If
End Sub
Private Sub TextBox13_AfterUpdate()
TextBox13.Text = Format(TextBox13.Text, "#,##0.00")
If TextBox13.Text <> "" Then
End If
End Sub
Private Sub TextBox14_AfterUpdate()
TextBox14.Text = Format(TextBox14.Text, "#,##0.00")
If TextBox14.Text <> "" Then
End If
End Sub
Private Function SumFood1() As Double
Dim i As Long
Dim tmp As Double
For i = 1 To 12
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumFood1 = tmp
End Function
Private Function SumOne1() As Double
Dim i As Long
Dim tmp As Double
For i = 2 To 3
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumOne1 = tmp
End Function
Private Function SumTwo1() As Double
Dim i As Long
Dim tmp As Double
For i = 6 To 7
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumTwo1 = tmp
End Function
Private Function SumThree1() As Double
Dim i As Long
Dim tmp As Double
For i = 10 To 11
With Me.Controls("TextBox" & i)
If IsNumeric(.Text) Then
tmp = tmp + CDbl(.Text)
End If
End With
Next i
SumThree1 = tmp
End Function[/VBA]
My Question is about TextBox14 this holds the Tax Rate of 14%
when UserForm Activated
this is my problem TextBox1
[VBA]Private Sub TextBox1_AfterUpdate()
TextBox1.Text = Format(TextBox1.Text, "#,##0.00")
If TextBox1.Text <> "" Then
End If
TextBox2.Text = Format(TextBox1.Text / 114 * 100, "#,##0.00")
TextBox13 = SumOne1 + SumTwo1 + SumThree1
End Sub[/VBA]
Instead of having / 114 * 100 i would like it to check TextBox14 first for the Value of the Tax rate and then continue.
I hope i explained it well. Snapshop to follow. The TextBoxes run from Left to right and Begin with TextBox1 to TextBox13 which is the Total
if i can fix TextBox1 code i will apply it to the others
Thanks for your time and help
Darren
South Africa
