March 17, 2014

# Automated Goods Service Tax GST Calculations Using Excel VBA

From: David K
Message: Hi expert, i needs some help to solve my problem.
Every quarter i have submit my company GST to IRA, and for this reason, I need a Sales & Purchase data workbook to key in all my quarterly sales & purchase figure to the workbook and get the GST figure after contra it from sales and purchase.
The update i want is very simple.
Always update data to the last empty rows of the worksheet. The percentage of GST is 7%

cell A1 input – is sales cost excluded GST
cell B1 input – is Purchase cost excluded GST
cell C1 input – is the Value of ( A1 – B1 )

cell E1 input – is GST value 7% calculate base on cell A1 actual sales cost
Cell F1 input – is GST value 7% calculate base on cell B1 actual purchase cost
cell G1 input – is the value of ( E1 – F1 )

My Problem
1) Some of my supplier they are not a GST register company. all these purchase are not allow to have any contra from the sales GST. so the value to cell F1 is either input as \$0 or N/A

2) Some of my supplier their invoice amount is inclusive of GST value. No separately GST figure is indicate in the invoice. the problem for me is that. whenever i do the update, i have to manually calculate and separate the invoice value into cost and GST and than update cost to cell b1 and update GST to cell F1 for doing this, is really wasting me a lot of time.

My Question & Needs Help

I was thinking how to create the UserForm to work in this way which there is some Option Button i can be select when i doing the data update

1 ) Option Button 1 selected. GST calculation
The update will automatically calculate the GST value base on the cost i have key in the UserForm and automatically add the GST value to cell F1

2 ) Option Button 2 selected. purchase value inclusive of GST
The update will automatically base on the cost i have key in in the UserForm and work up the actual cost & GST and than update ( actual cost to cell B1 and GST value to cell F1 )

3) Option Button 3 selected. purchase value without GST
The update will automatically add a \$0.00 value or N/A in cell F1

Any help to solve my problem will be greatly appreciate

Watch the video below to see how we created the userform and coded the controls appropriately using Excel VBA:

below the video:

Here is the complete Excel VBA macro code:

Private Sub CommandButton1_Click()
Sheet1.Activate
If ListBox1.Value = “Invoice with no GST” Then
TextBox3.Value = 0
End If

If ListBox1 = “Invoice with separate 7% GST” Then
TextBox3.Value = TextBox2.Value * 0.07
End If

If ListBox1 = “Invoice inclusive of 7% GST” Then
TextBox3.Value = TextBox2.Value – (TextBox2.Value / 1.07)
TextBox2.Value = TextBox2.Value – TextBox3.Value
End If

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Value = ComboBox1.Value
Cells(erow, 2).Value = ComboBox2.Value
Cells(erow, 3) = TextBox2.Value
Cells(erow, 3).NumberFormat = “\$#,##0.00”
Cells(erow, 4).Value = TextBox3.Value
Cells(erow, 4).NumberFormat = “\$#,##0.00”
Cells(erow, 5).Value = TextBox4.Value
Cells(erow, 5).Value = Format(Date, “mm/dd/yyyy”)

End Sub

Private Sub CommandButton2_Click()
TextBox1.Value = “”
ComboBox1.Value = “”
ComboBox2.Value = “”
TextBox2.Value = “”
TextBox3.Value = “”
TextBox4.Value = “”
End Sub

Private Sub CommandButton3_Click()