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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,627
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.

Thanks in advance
Kelly
 

RSpin

New Member
Joined
Dec 12, 2018
Messages
31
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
Joined
Apr 10, 2017
Messages
1,627
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
Joined
Dec 12, 2018
Messages
31
Glad I could help.
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
Joined
Apr 10, 2017
Messages
1,627
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
Joined
Dec 12, 2018
Messages
31
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
Joined
Apr 10, 2017
Messages
1,627
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
Joined
Dec 12, 2018
Messages
31
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.
 

RSpin

New Member
Joined
Dec 12, 2018
Messages
31
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:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top