Automated Goods & Service Tax (GST) Calculations Using Excel VBA

Home More Excel (VBA) Training Videos


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


Thanks you in advance

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


Watch this video on YouTube


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()
Unload Me
End Sub

Private Sub ListBox1_Click()
TextBox1.Value = ListBox1.Value
End Sub

Home More Excel (VBA) Training Videos