Adjust code to cater for multiple combo boxes

[sueanne]

Hi,

I am having great difficulty trying to work out how to calculate GST on a userform. I apologise for the duplicate post, however the other post probably didn't explain it very well.

I have managed to get the code below to work for cboTax1 however I cannot work out how to adjust this code to cater for multiple combo boxes.
By that I mean, I have cboTax1-10 and I need to perform this calculation for all 10 boxes and put the total of the results to Taxamt Textbox.

Private Sub cboTax1_change()
If cboTax1.Value = "GST Free" Then
 Taxamt1 = "$0.00"
Else
Taxamt1 = (Total1.Value * (10 / 100))
 End If
Taxamt.Value = Format(Taxamt1, "$#,##0.00")
 End Sub

If anyone can please help that would be greatly appreciated, I have been searching the net for days now, but just cant find anything that works for what I want. Maybe I cant do it how I want? 

Thanks so much!
Sue-Anne

 

[sueanne]

hippiehacker,

thank you so much, the workbook you sent back calculates the tax, updates on change to gst code, and populates the total amount field. The way you have made the subtotal calculate is much easier than putting in that code I had for each total box change.

I have an issue, being that they now want a second version of the tax invoice, the second one to be able to enter in the unit price manually, and remove the item code field off the user form totally. The Unit price will be a textbox.

I can get the 10 total boxes to still populate, however I obviously need to adjust your code, as it wont be looking up cboIC anymore. The total box will still populate, Qty x Unit Price - Disc. Then I still need the tax to be calculated from whether cbotax is GST or GST Free, and work out the tax amount based on value in the total textbox. However, I need it to update if the Qty or Unit Price is changed as well as if the cbotax is changed once selected/entered.
So if they enter Qty as 1 then change to 2, it needs to update the tax amount. If they change from GST to GST free, needs to update (which it does), if they change the unit price from $10 to $15, needs to update tax amount.

With the discount, the first line is populated via vlookup from the customer name, in the customer table. I haven't populated the other lines yet, as I want to put a control on the sheet so that they cant enter on any of the other lines until line 1 is completely populated.

I am going to play around with it to see if I can work out how to modify your code to make it work without cboIC being involved at all. I must admit that your code is well above my skill levels, so I am hoping I can work out what I need to change and get it to work.

I know it is annoying when something changes, I appreciate all the time and effort you have put in to helping me with this.

 

[sueanne]

I think you have already worked this out, but the tax is 10% of the total price.

 

[hippiehacker]

sueanne I've sent you an updated version of your Workbook all userform calculation is now being done in the module

have alook at the calculation if all is working.
I guess the Discount calculation is incorrect
example now:
Unit Price = $10
discount = 1%
Total Price its calculated as 9$
but it should be
Unit Price = $10
discount = 1%
Total Price should be $9.9 ?? or i might be wrong on this ???

 

[sueanne]

hippiehacker, thank you so much I am just going through it now and having a look.

Yes that is right about the discount, it is picking up the disc value as a number and not a percentage. I did have it working properly in a previous version, however I must have deleted something that converted it to a %.

I will let you know how I go

 

[sueanne]

Hippiehacker,

I have copied all the code you provided into my updated workbook, however I am getting the following error message when I click to go into Taxinvoice userform.

Run-time error '1004': Application-defined or object defined error.

I have no idea what this means, but when I click on debug it takes me to the following code and highlights Taxinvoice.Show in yellow.

Private Sub cb_EnterInvoice_Click()
Me.Hide
Taxinvoice.Show
End Sub

 

[sueanne]

hippiehacker,

I have just sent you a copy of my workbook prior to putting in the code you provided, and also a copy with the code put in that produces the above mentioned error when trying to load the taxinvoice.

As you will see from the updated workbook, there is no IC field and the unit price is manually entered in now (have to format to be a $ value still). I have been trying to adapt your code to cater for this amended version of the taxinvoice, I thought I could just remove the clsCommonComboBox class module and the sub sue_anne and combo_IC_change but that didn't work, and I cant test it in a copy of my current workbook because I keep getting the above error message.

What should I do here?

 

[hippiehacker]

if you removed the IC Comboboxes then the whole code has to be rewritten as those fields was required for the calculations
the error is due to removed IC comboboxes from the Userform it tries to fill them at the userform initialize event

i have rewritten the calculations without those comboboxes now

Ive sent you the book

 

[sueanne]

Oh thank you, sorry about the change and you having to re-write the code.

The GST is calculating now, thank you! However a couple of other fields are not populating correctly.

a) The discount is populating as 0% in all lines once the customer is selected. ie. Smith Company should populate as 1%
b) The Total Price is not auto calculating from the Qty x Unit Price (needs to update if either of these fields are changed)
c) If the user does not enter in the $ sign with the value in Unit Price it doesn't calculate, can a prompt come up to make sure they enter as a $ figure?

I am not sure what to change in the code without breaking it, I am looking through the code now to see if I can see anything that stands out.

Thank you again for your help!

 

[sueanne]

Ok I have fixed the above points, just working out how to fix the total calculation so it picks the discount up as a % and not a value. ie. qty x up = $104 disc is 1%. It is populating total box as $103 instead of $102.96

 

[sueanne]

Also if you enter unit price as $x.xx it doesn't like the decimal places and doesn't calculate the total value.