Excel VBA check if values exist in various comboboxes then add corresponding textbox values

Question

I apologize in advance for writing to ask for help if you all think this is a stupid question.

I have a userform with 6 comboboxes and 6 textboxes that I can enter values into.

Combobox1   Textbox1
Combobox2   Textbox2
Combobox3   Textbox3
Combobox4   Textbox4
Combobox5   Textbox5
Combobox6   Textbox6

This question requires knowledge of combinations/permutations.

First i enter a code in one of the comboboxes, and then a total CY in the textbox next to it. Then i click a commandbutton to give me a report. What i'm trying to achieve is to write a code that can look in each of the 6 comboboxes, see if code "131010" exist in some of them, if yes, then add textbox1... while excluding textbox2 (if the code is not in combobox2).

So if only combobox1, 3, 5, 6 have that code, i need to only add textbox1, 3, 5, 6 while excluding textbox4.

So far i have the following code:

If ComboBox1.Value = "131010" Or ComboBox3.Value = "131010" Or ComboBox5.Value =   "131010" Or ComboBox7.Value = "131010" Or ComboBox9.Value = "131010" Or ComboBox11.Value = "131010" Then 
TextBox59.Value = Val(TextBox6) + Val(TextBox11) + Val(TextBox16) + Val(TextBox21) + Val(TextBox26) + Val(TextBox31)
TextBox52.Value = ComboBox1.Value
Else

or i have found this one code:

For i = 1 To 6
    If IsNull(Me.Controls("combobox" & i)) = False Then
    if combobox & i="131010" then
        textbox59=val(textbox6)+val(textbox & i+5)
    End If
Next i

The VBA code is adding all the textboxes and does not recognize when there is different cost code in a combobox. I can see what i'm doing wrong, but i'm not sure how to fix it. Can someone please help me figure out how to fix it? or perhaps propose another alternative? perhaps i need to loop through the comboboxes...i don't know how to do this. Please don't give a negative for this question. I really did researched this at length and could not come up with something.

thank you all in advance.

Floris Floris · Accepted Answer · 2014-03-04T19:24:53

You can loop through the comboboxes like this:

' at top of module:
 Option Compare Text   ' make text comparison case insensitive

' in function:
Dim s as string
s = ""
for each b in UserForm.Controls
  If left(b.Name, 8) = "combobox" Then
    ' you found a combobox and its name
    ' you can append things to s as needed.
  End If
Next

Alternatively you can loop through all of them first and generate an array of values for easy manipulation:

Dim boxSelection(6)
Dim textValues(6) As String
Dim b
For Each b In UserForm1.Controls
  If Left(b.Name, 8) = "ComboBox" Then
    Index = Right(b.Name, 1)
    boxSelection(Index) = b.Value
  End If
  If Left(b.Name, 7) = "TextBox" Then
    Index = Right(b.Name, 1)
    textValues(Index) = b.Value
  End If
Next

You end up with two arrays of length 6 - I hardcoded that based on your example - which contain the values for each combobox. Now you can use "easier" VBA routines to compare, combine, whatever.

UPDATE assuming your textboxes are called "textbox1" through "textbox6" (as in your example above, although in the code they seem to have different names...), and your comboboxes are calles "combobox1" through "combobox6" (again, if needed, I recommend that you rename them to match that - makes life easier), then you can do

Dim totalValue
totalValue = 0
for each b in UserForm.Controls
  If left(b.Name, 8) = "combobox" Then
    index = Right(b.name, 1)
    If b.Value = "131010" Then
      totalValue = totalValue & UserForm.Controls("textbox" & index).value
    End If
  End If
Next

A few key things (which I thought you could have figured out from the code snippets I posted earlier - but I was obviously wrong):

You automatically loop through all the comboboxes
You look to see if the name matches what you are looking for
The last character of the name is a number
You find the corresponding numbered "textbox"
You accumulate the values into totalValue

Do you think you can take it from here? I am wondering why you are doing all this with a user form - seems like the wrong tool for the job - but given that you do, the above (or a variant of it) should work.

Note - you DO have to rename the controls...

Excel VBA check if values exist in various comboboxes then add corresponding textbox values

1 Answers