# Sum values of six textboxes out of 9 textboxes with a condition

#### kelly mort

##### Well-known Member
Hello and merry Christmas to you (if you do celebrate it)

I need help with this challenge :

There are these 9 textboxes on my userform that I want to add values from six of them and place the result in the 10th textbox.

Now these are the rules for the summing :

1. Add the first 4 boxes if all first 4 are none empty. Then added the first two smallest values from the rest of the textboxes.
2. If one of the first 4 boxes is empty then add the three none empty ones and add first three smallest values from the remaining textboxes.
3. If two boxes are empty from the first 4 textboxes then add the two none empty and add first 4 smallest values of the rest of the textboxes. This should follow in that order.

I am stacked with the algorithm.

I have the feeling someone here can fix it for me.

Kelly

#### RSpin

##### New Member
I think this will do what you're wanting:
Code:
``````Private Sub TextBox1_Change()
Calc
End Sub
Private Sub Calc()
Dim AA As Integer
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer
Dim OutPut As Double
Dim TBused(1 To 9) As Variant
AA = 0
'test for 6 textboxes filled in
For BB = 1 To 9
If IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
AA = AA + 1
End If
Next BB
If AA < 6 Then
Exit Sub
End If
' Find the 6 numbers and add them together
OutPut = 0
For AA = 1 To 6
For BB = 1 To 9
If TBused(BB) <> "Used" And IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
If BB > 4 Then
CC = 0
For DD = 5 To 9
If TBused(DD) <> "Used" And IsNumeric(Me.Controls("TextBox" & DD).Value) = True Then
If CC = 0 Then
CC = DD
Else
If Val(Me.Controls("TextBox" & DD).Value) < Val(Me.Controls("TextBox" & CC).Value) Then
CC = DD
End If
End If
End If
Next DD
OutPut = OutPut + Val(Me.Controls("TextBox" & CC).Value)
TBused(CC) = "Used"
Exit For
Else
OutPut = OutPut + Val(Me.Controls("TextBox" & BB).Value)
TBused(BB) = "Used"
Exit For
End If
End If
Next BB
Next AA
'put the sum of the 6 numbers into the textbox
Me.TextBox10.Value = OutPut
End Sub
Private Sub TextBox2_Change()
Calc
End Sub
Private Sub TextBox3_Change()
Calc
End Sub
Private Sub TextBox4_Change()
Calc
End Sub
Private Sub TextBox5_Change()
Calc
End Sub
Private Sub TextBox6_Change()
Calc
End Sub
Private Sub TextBox7_Change()
Calc
End Sub
Private Sub TextBox8_Change()
Calc
End Sub
Private Sub TextBox9_Change()
Calc
End Sub``````
This will automatically update textbox10 as the inputs to textboxes 1-9 are changed.

#### kelly mort

##### Well-known Member
I think this will do what you're wanting:
Code:
``````Private Sub TextBox1_Change()
Calc
End Sub
Private Sub Calc()
Dim AA As Integer
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer
Dim OutPut As Double
Dim TBused(1 To 9) As Variant
AA = 0
'test for 6 textboxes filled in
For BB = 1 To 9
If IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
AA = AA + 1
End If
Next BB
If AA < 6 Then
Exit Sub
End If
' Find the 6 numbers and add them together
OutPut = 0
For AA = 1 To 6
For BB = 1 To 9
If TBused(BB) <> "Used" And IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
If BB > 4 Then
CC = 0
For DD = 5 To 9
If TBused(DD) <> "Used" And IsNumeric(Me.Controls("TextBox" & DD).Value) = True Then
If CC = 0 Then
CC = DD
Else
If Val(Me.Controls("TextBox" & DD).Value) < Val(Me.Controls("TextBox" & CC).Value) Then
CC = DD
End If
End If
End If
Next DD
OutPut = OutPut + Val(Me.Controls("TextBox" & CC).Value)
TBused(CC) = "Used"
Exit For
Else
OutPut = OutPut + Val(Me.Controls("TextBox" & BB).Value)
TBused(BB) = "Used"
Exit For
End If
End If
Next BB
Next AA
'put the sum of the 6 numbers into the textbox
Me.TextBox10.Value = OutPut
End Sub
Private Sub TextBox2_Change()
Calc
End Sub
Private Sub TextBox3_Change()
Calc
End Sub
Private Sub TextBox4_Change()
Calc
End Sub
Private Sub TextBox5_Change()
Calc
End Sub
Private Sub TextBox6_Change()
Calc
End Sub
Private Sub TextBox7_Change()
Calc
End Sub
Private Sub TextBox8_Change()
Calc
End Sub
Private Sub TextBox9_Change()
Calc
End Sub``````
This will automatically update textbox10 as the inputs to textboxes 1-9 are changed.
Great great!!!

Exactly and even powerful than I expected.

One thung:
I want the value in textboxe10 cleared when the used textboxes drops below 6.

Thanks

#### RSpin

##### New Member
To get the textbox cleared, change:
Code:
``````If AA < 6 Then
Exit Sub
End If``````
to:
Code:
``````If AA < 6 Then
Me.TextBox10.Value = ""
Exit Sub
End If``````

#### kelly mort

##### Well-known Member
Great!

I need explanation to :

1. Dim TBUsed (1 To 9) As Variant
2. "Used".

I don't understand what they are doing
Regards
Kelly

#### RSpin

##### New Member
No problem.

"Dim TBUsed(1 to 9) as Variant" is creating an array. In this case, it's similar to creating 9 variables that can hold data. When one of the numbers, from a textbox, is put into the sum, the corrisponding slot in the array is marked with the word "Used" (so if the number in TextBox3 is added to the sum, then TBUsed(3) would be marked "Used"). When the code checks for a number in a textbox, it is also checking the array, in the corrisponding slot, to see if it is marked "Used" and if so, it skips the number. The purpose is to make sure no number gets used more than once in the sum.

I hope that helps.

#### kelly mort

##### Well-known Member
Very useful. Does it mean that when I start my textboxes from say textbox11 to textbox19 for the nine textboxes then I have to get

Dim TBused ( 11 To 19) As Variant?

Looking very tricky. Lol

#### RSpin

##### New Member
Yes it does.

If you're planning to use the code for textboxes outside of the 1 to 10 range, then there are several other things you would also need to change as well:

Both "For BB = 1 to 9 " loops would need the numbers to change to the textboxes being used.
The "If BB > 4 then" line would need to change to the 4th textbox.
The "For DD = 5 to 9" would need to change to the last 5 textboxes.
The line "me.TextBox10.value = Output" would need to change to reflect the new textbox that will show the sum.
And each new textbox would a sub that runs the Calc sub.

Sorry for it being so complicated.

#### kelly mort

##### Well-known Member
No problem.

Challenge accepted

#### RSpin

##### New Member
ok, tried to reduce the things you would need to change in this new iteration of the code:
Code:
``````Private Sub TextBox1_Change()
Calc
End Sub
Private Sub Calc()
Dim FirstBox As Integer
Dim LastBox As Integer
Dim OutPutBox As Integer
Dim AA As Integer
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer
Dim OutPut As Double
Dim TBused() As Variant

FirstBox = 1
LastBox = 9
OutPutBox = 10

ReDim TBused(FirstBox To LastBox)
AA = 0
'test for 6 textboxes filled in
For BB = FirstBox To LastBox
If IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
AA = AA + 1
End If
Next BB
If AA < 6 Then
Me.Controls("TextBox" & OutPutBox).Value = ""
Exit Sub
End If
' Find the 6 numbers and add them together
OutPut = 0
For AA = 1 To 6
For BB = FirstBox To LastBox
If TBused(BB) <> "Used" And IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
If BB > (FirstBox + 3) Then
CC = 0
For DD = (FirstBox + 4) To LastBox
If TBused(DD) <> "Used" And IsNumeric(Me.Controls("TextBox" & DD).Value) = True Then
If CC = 0 Then
CC = DD
Else
If Val(Me.Controls("TextBox" & DD).Value) < Val(Me.Controls("TextBox" & CC).Value) Then
CC = DD
End If
End If
End If
Next DD
OutPut = OutPut + Val(Me.Controls("TextBox" & CC).Value)
TBused(CC) = "Used"
Exit For
Else
OutPut = OutPut + Val(Me.Controls("TextBox" & BB).Value)
TBused(BB) = "Used"
Exit For
End If
End If
Next BB
Next AA
'put the sum of the 6 numbers into the textbox
Me.Controls("TextBox" & OutPutBox).Value = OutPut
End Sub
Private Sub TextBox2_Change()
Calc
End Sub
Private Sub TextBox3_Change()
Calc
End Sub
Private Sub TextBox4_Change()
Calc
End Sub
Private Sub TextBox5_Change()
Calc
End Sub
Private Sub TextBox6_Change()
Calc
End Sub
Private Sub TextBox7_Change()
Calc
End Sub
Private Sub TextBox8_Change()
Calc
End Sub
Private Sub TextBox9_Change()
Calc
End Sub``````
In this version, the only things you should need to do is:
Change the value of FirstBox to the first textbox number. (11 in the case above)
Change the value of LastBox to the last textbox number. (19 in the case above)
Change the value of OutPutBox to the textbox number of the textbox you want to show the results.
And, add a sub that runs Calc to each of the new textboxes. Such as this for textbox11:
Code:
``````Private Sub TextBox11_Change()
Calc
End Sub``````
One piece of code that has changed is: "Dim TBused() as variant" and then "Redim TBused(FirstBox to LastBox)".
What this does is create the array, but with no initial settings. Then change the array to use the size of that is needed, based on the variables you have put in. The reason this is two steps is that VBA throws fits when you initially create the array using variables ( I don't know why it cares, but it does.)

Last edited:

1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...