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: 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 