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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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