Adjust code to cater for multiple combo boxes

sueanne

Board Regular
Joined
Apr 2, 2014
Messages
81
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.

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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Total1.Value is a dollar value, based on Item Code x Unit Price

Below is the code I have in for each of the Total boxes

Code:
Private Sub cboIC1_AfterUpdate()
If UP1.Value = "" Then Exit Sub
Total1.Value = Format(CInt(Qty1.Value * UP1.Value) - (CInt(Val(Disc1))), "$#,##0.00")
End Sub
Private Sub Qty1_AfterUpdate()
If UP1.Value = "" Then Exit Sub
Total1.Value = Format(CInt(Qty1.Value * UP1.Value) - (CInt(Val(Disc1))), "$#,##0.00")
End Sub
 
Upvote 0
Hi
something along the lines of ...
Code:
Option Explicit
Private Sub ComboBox1_Change()
Call Textbox_Calx
End Sub
Private Sub ComboBox2_Change()
Call Textbox_Calx
End Sub
Private Sub ComboBox3_Change()
Call Textbox_Calx
End Sub
Private Sub ComboBox4_Change()
Call Textbox_Calx
End Sub
Function ComboBox_Calx(ComboBoxIndex) As Long
If ComboBoxIndex = vbNullString Or ComboBoxIndex = "GST Free" Then
   ComboBox_Calx = 0
  Else
  ComboBox_Calx = Format(CLng(ComboBoxIndex) * (10 / 100), "$#,##0.00")
   End If
End Function
Sub Textbox_Calx()
    Dim cCont As Control, TextValue As Long
    For Each cCont In Me.Controls
        If TypeName(cCont) = "ComboBox" Then
            TextValue = TextValue + ComboBox_Calx(cCont.Value)
        End If
    Next cCont
    Me.TextBox1.Value = Format(TextValue, "$#,##0.00")
End Sub
 
Upvote 0
userform code:
Code:
Dim tbCollection As Collection

Private Sub UserForm_Initialize()
    Dim ctrl As MSForms.Control
    Dim obj As clsCommonComboBox

    Set tbCollection = New Collection
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is MSForms.ComboBox Then
                Set obj = New clsCommonComboBox
                Set obj.Control = ctrl
                tbCollection.Add obj
            End If
        Next ctrl
    Set obj = Nothing

End Sub

class code: please rename the classmodule to clsCommonComboBox in the Properties window
Code:
Private WithEvents MyCombobox As MSForms.ComboBox

Public Property Set Control(tb As MSForms.ComboBox)
    Set MyCombobox = tb
End Property
Private Sub MyCombobox_Change()
    Call sue_anne
End Sub

normal module code:
Code:
Public taxamt1 As Double
Public Total1 As Double

Sub sue_anne()
 taxamt1 = 0
  Dim cCont As Control
   For Each cCont In UserForm1.Controls
      If TypeName(cCont) = "ComboBox" Then
           If cCont.Text = "GST Free" Or cCont.Text = "" Then
            taxamt1 = taxamt1 + 0
            Else
             taxamt1 = taxamt1 + (Total1.Value * (10 / 100))
           End If
        End If
     Next cCont
    UserForm1.TextBox1.Value = Format(taxamt1, "$#,##0.00")
End Sub
 
Upvote 0
using isnull and isnumeric may void a few errors
Rich (BB code):
Function ComboBox_Calx(ComboBoxIndex) As Long
    If IsNull(ComboBoxIndex) Or Not IsNumeric(ComboBoxIndex) Then
        ComboBox_Calx = 0
    Else
        ComboBox_Calx = Format(CLng(ComboBoxIndex) * (10 / 100), "$#,##0.00")
    End If
End Function

also to capture your code with code tags you need the end bit eg.. [code=rich] your code/syntax [/code]
 
Last edited:
Upvote 0
Hi Pike,

Firstly thank you for your response!

I have put that code into my workbook, and modified it from Combobox_change() to be cboTax1_change() through to cboTax4_change(). I changed the Me.Textbox1.Value to be me.Taxamt.Value as that's the box I want to populate with the result.

However, when I put that in, and I populate the first line of the userform with :

Qty 1
UP $9
Total1 = $9
Tax = GST

The value being populated in the taxamt textbox is $3. I cant work out how it is picking up $3?

Also, if I change the line to be GST Free it doesn't change the value in the taxamt box.

Is it because there are other comboboxes on the worksheet that are not to be included in this calculation?

Would it be easier if I created an extra field which calculated the gst for each line, and then just sum up those lines?

Sorry I am new to VBA and I cant work out from my very basic knowledge why this isn't calculating out correctly.

Thanks for your assistance! :)
 
Upvote 0
Hi Hippiehacker,

Thank you for your reply also!

I have also tried the code you have provided, and I get a compile error " Variable not defined" at the sub sue_anne() point.
 
Upvote 0
can you send me tyhe book so I could have a look I guess it has to do with the taxamt and total values
 
Upvote 0
Ok no worries, I don't think I have permissions to attach files in here. How should I send it to you?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top