PDA

View Full Version : Solved: Value Added Tax conversion problem

Darren
09-07-2007, 04:21 AM
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

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

My Question is about TextBox14 this holds the Tax Rate of 14%
when UserForm Activated

this is my problem TextBox1

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

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

Darren
09-07-2007, 04:24 AM
This what it look like current but i would like to be able to change the tax rate and the calculations should follow

rory
09-07-2007, 04:59 AM
You can use something like:
Dim dblRate as Double
dblRate = Val(Textbox14)

and then refer to (dblRate + 100) instead of 114.