I have a userform with 21 textboxes i need to get the Sum of.
How can i code this?
The textboxes are not in indexed in order either.
They a
textbox6
textbox13
textbox22
textbox31
textbox39
textbox48
textbox56
textbox64
textbox72
textbox80
textbox88
textbox96
textbox104
textbox112
textbox120
textbox128
textbox136
textbox144
textbox152
textbox160
&
textbox168

I need to get the sum of ALL these textbox.values to be Calculated and
placed in textbox172 as the user fills in the values in the textboxes above.


Corey....

Corey wrote:
I have a userform with 21 textboxes i need to get the Sum of.
How can i code this?
The textboxes are not in indexed in order either.
They a
textbox6
textbox13
textbox22
textbox31
textbox39
textbox48
textbox56
textbox64
textbox72
textbox80
textbox88
textbox96
textbox104
textbox112
textbox120
textbox128
textbox136
textbox144
textbox152
textbox160
&
textbox168

I need to get the sum of ALL these textbox.values to be Calculated and
placed in textbox172 as the user fills in the values in the textboxes above.


Corey....

Hi Corey,

This code in the Change event of each of the 21 TextBoxes worked for
me. There could be an easier way though (I just don't know it:-))...

Private Sub TextBox6_Change()
Dim TxBx As Control
Const T As String = "TextBox"
Dim sngTxBx172Total As Single
For Each TxBx In UserForm1.Controls
Select Case TxBx.Name
Case T & 6, T & 13, T & 22, T & 31, T & 39, T & 48, T & 56, _
T & 64, T & 72, T & 80, T & 86, T & 104, T & 112, T & 120, _
T & 128, T & 136, T & 144, T & 152, T & 160, T & 168
If IsNumeric(TxBx.Text) Then
sngTxBx172Total = sngTxBx172Total + TxBx.Text
End If
End Select
Next TxBx
UserForm1.TextBox172.Text = sngTxBx172Total
End Sub

Ken Johnson

Here is a start...

Function AddThemUp()
Dim objCtrl As msforms.Control
Dim N As Long
For Each objCtrl In Me.Controls
If TypeOf objCtrl Is msforms.TextBox Then
N = N + Val(objCtrl.Value)
End If
Next
MsgBox N
End Function

Private Sub CommandButton1_Click()
Call AddThemUp
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Corey"
wrote in message
I have a userform with 21 textboxes i need to get the Sum of.
How can i code this?
The textboxes are not in indexed in order either.
They a
textbox6
textbox13
textbox22
textbox31
textbox39
textbox48
textbox56
textbox64
textbox72
textbox80
textbox88
textbox96
textbox104
textbox112
textbox120
textbox128
textbox136
textbox144
textbox152
textbox160
&
textbox168

I need to get the sum of ALL these textbox.values to be Calculated and
placed in textbox172 as the user fills in the values in the textboxes above.
Corey....

I stuffed up those textbox numbers, so try...

Private Sub TextBox6_Change()
Dim TxBx As Control
Const T As String = "TextBox"
Dim sngTxBx172Total As Single
For Each TxBx In UserForm1.Controls
Select Case TxBx.Name
Case T & 6, T & 13, T & 22, T & 31, T & 39, T & 48, T & 56, _
T & 64, T & 72, T & 80, T & 88, T & 96, T & 104, T & 112, _
T & 120, T & 128, T & 136, T & 144, T & 152, T & 160, T & 168
If IsNumeric(TxBx.Text) Then
sngTxBx172Total = sngTxBx172Total + TxBx.Text
End If
End Select
Next TxBx
UserForm1.TextBox172.Text = sngTxBx172Total
End Sub

instead.

Ken Johnson

I've copied part of Jim's method (he won't mind) and used one sub
procedure which is then called by each of the 21 textbox's Change
event...

Public Sub AddSomeTxBxs()
Dim TxBx As Control
Const T As String = "TextBox"
Dim sng72Total As Single
For Each TxBx In UserForm1.Controls
Select Case TxBx.Name
Case T & 2, T & 4, T & 6, T & 8, T & 10, T & 12, T & 14, T & 16
If IsNumeric(TxBx.Text) Then
sng72Total = sng72Total + TxBx.Text
End If
End Select
Next TxBx
UserForm1.TextBox72.Text = sng72Total
End Sub

Private Sub TextBox6_Change()
AddSomeTxBxs
End Sub

Private Sub TextBox13_Change()
AddSomeTxBxs
End Sub

etc, etc

Ken Johnson

Nevermind my festival of errors!

Try...

Public Sub AddSomeTxBxs()
Dim TxBx As Control
Const T As String = "TextBox"
Dim sng172Total As Single
For Each TxBx In UserForm1.Controls
Select Case TxBx.Name
Case T & 6, T & 13, T & 22, T & 31, T & 39, T & 48, T & 56, _
T & 64, T & 72, T & 80, T & 88, T & 96, T & 104, T & 112, _
T & 120, T & 128, T & 136, T & 144, T & 152, T & 160, T & 168
If IsNumeric(TxBx.Text) Then
sng172Total = sng172Total + TxBx.Text
End If
End Select
Next TxBx
UserForm1.TextBox172.Text = sng172Total
End Sub

Private Sub TextBox6_Change()
AddSomeTxBxs
End Sub

Private Sub TextBox13_Change()
AddSomeTxBxs
End Sub

etc, etc

Ken Johnson

thank you for you time Ken.
Perfectly Done.


Corey....
P.S Wont mention my typo's in my post.(172 was suppose to be 173)
:-)

"Ken Johnson" wrote in message
ups.com...
Nevermind my festival of errors!

Try...

Public Sub AddSomeTxBxs()
Dim TxBx As Control
Const T As String = "TextBox"
Dim sng172Total As Single
For Each TxBx In UserForm1.Controls
Select Case TxBx.Name
Case T & 6, T & 13, T & 22, T & 31, T & 39, T & 48, T & 56, _
T & 64, T & 72, T & 80, T & 88, T & 96, T & 104, T & 112, _
T & 120, T & 128, T & 136, T & 144, T & 152, T & 160, T & 168
If IsNumeric(TxBx.Text) Then
sng172Total = sng172Total + TxBx.Text
End If
End Select
Next TxBx
UserForm1.TextBox172.Text = sng172Total
End Sub

Private Sub TextBox6_Change()
AddSomeTxBxs
End Sub

Private Sub TextBox13_Change()
AddSomeTxBxs
End Sub

etc, etc

Ken Johnson

Corey wrote:
thank you for you time Ken.
Perfectly Done.


Corey....
P.S Wont mention my typo's in my post.(172 was suppose to be 173)
:-)

"Ken Johnson" wrote in message
ups.com...
Nevermind my festival of errors!

Try...

Public Sub AddSomeTxBxs()
Dim TxBx As Control
Const T As String = "TextBox"
Dim sng172Total As Single
For Each TxBx In UserForm1.Controls
Select Case TxBx.Name
Case T & 6, T & 13, T & 22, T & 31, T & 39, T & 48, T & 56, _
T & 64, T & 72, T & 80, T & 88, T & 96, T & 104, T & 112, _
T & 120, T & 128, T & 136, T & 144, T & 152, T & 160, T & 168
If IsNumeric(TxBx.Text) Then
sng172Total = sng172Total + TxBx.Text
End If
End Select
Next TxBx
UserForm1.TextBox172.Text = sng172Total
End Sub

Private Sub TextBox6_Change()
AddSomeTxBxs
End Sub

Private Sub TextBox13_Change()
AddSomeTxBxs
End Sub

etc, etc

Ken Johnson


Hi Corey,

You're welcome.

It's nice to know I'm not the only stufferupperer.

Ken Johnson

Hi Corey,

This code in the Change event of each of the 21 TextBoxes worked for
me. There could be an easier way though (I just don't know it:-))...

Private Sub TextBox6_Change()
Dim TxBx As Control
Const T As String = "TextBox"
Dim sngTxBx172Total As Single For Each TxBx In UserForm1.Controls
Select Case TxBx.Name
Case T & 6, T & 13, T & 22, T & 31, T & 39, T & 48, T & 56, _
T & 64, T & 72, T & 80, T & 86, T & 104, T & 112, T & 120, _
T & 128, T & 136, T & 144, T & 152, T & 160, T & 168
If IsNumeric(TxBx.Text) Then
sngTxBx172Total = sngTxBx172Total + TxBx.Text
End If
End Select
Next TxBx
UserForm1.TextBox172.Text = sngTxBx172Total
End Sub

Ken Johnson


This sounds like a good plan, the only thing I would add is to have a
global constant somewhere that defines these textboxes. A similar
solution is to have an array that populates at startup, then looping
through the array to add the values.

Finally, you could (and honestly probably should) give the textboxes
more meaningful names. You will have to rename any references
throughout your code, if the textboxes are programmatically addressed
anywhere else.

Just to give you an idea, here is some code I use in a timesheet
program to add up a bi-weekly hours total. This is actually an Access
program, but would work equally well in Excel. I use this technique
throughout the module.


The idea is to have all names following this convention:
[type][dayNumber][fieldName]

[type] = 3-character description of the fields; txt = textbox, cmb =
combobox, lbl = label, etc.
[dayNumber] = an integer from 1 to 14
[fieldName] = a short description of what the data is; e.g. Hours,
Detail, Time In/Out, etc

In this portion of the code, a message box pops up showing detail of
each daily item - in our industry, certain consultants do group
sessions, thus there can be multiple cost centers and detail for each
item needs to be tracked. This procedure is passed a string value for
the Day # for which detail is to be displayed. The referenced control
is resolved at runtime (admittedly slower) to extract the detail.

The timesheet form has a combo box with multiple columns - total
billable hours, time in/out, client name, cost center code, and a
description. The higher-level loop cycles through each row in the
combo box containing detail (using a recordset), whereas the inner loop
cycles through each column.

As applicable to your situation, each member of Me (the form in which
the controls reside) is addressed by a string-literal denoting the
control name. In your program, if it is possible to rename the
textboxes with a number somewhere in the name denoting the sequence,
you can loop through them using this technique.

Here is the code:

Private Sub showDetail(strDayValue As String)
Dim Message As String
Dim countRow As Integer, countCol As Integer
Dim totalRows As Integer

If (Me("cmb" & strDayValue & "Info").ListCount < 1) Then
Message = "There is no detail to display."
Call MsgBox(Message, vbOKOnly + vbExclamation, "No detail for
date selected.")
Exit Sub
End If

Dim rsDetail As New ADODB.Recordset

With rsDetail
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open (Me("cmb" & strDayValue & "Info").RowSource)
If (.AbsolutePosition < 1) Then
.MoveFirst
End If
For countRow = 1 To .RecordCount
'zero-based index, skip first column
For countCol = 2 To Me("cmb" & strDayValue &
"Info").ColumnCount
Message = Message & rsDetail(countCol - 1) & vbTab
'last record will be the description, so add new line
before moving to it
If countCol = Me("cmb" & strDayValue &
"Info").ColumnCount - 1 Then
Message = Message & vbCrLf
End If
Next countCol
Call MsgBox(Message, vbOKOnly + vbInformation, _
"Detail for " & Me("lbl" & strDayValue &
"Date").Caption & _
"; " & vbTab & countRow & " of " &
..RecordCount)
Message = ""
.MoveNext
Next countRow
.Close
End With
Set rsDetail = Nothing
End Sub


Ken Johnson wrote:
Corey wrote:
I have a userform with 21 textboxes i need to get the Sum of.
How can i code this?
The textboxes are not in indexed in order either.
They a
textbox6
textbox13
textbox22
textbox31
textbox39
textbox48
textbox56
textbox64
textbox72
textbox80
textbox88
textbox96
textbox104
textbox112
textbox120
textbox128
textbox136
textbox144
textbox152
textbox160
&
textbox168

I need to get the sum of ALL these textbox.values to be Calculated and
placed in textbox172 as the user fills in the values in the textboxes above.


Corey....

Hi Corey,

This code in the Change event of each of the 21 TextBoxes worked for
me. There could be an easier way though (I just don't know it:-))...

Private Sub TextBox6_Change()
Dim TxBx As Control
Const T As String = "TextBox"
Dim sngTxBx172Total As Single
For Each TxBx In UserForm1.Controls
Select Case TxBx.Name
Case T & 6, T & 13, T & 22, T & 31, T & 39, T & 48, T & 56, _
T & 64, T & 72, T & 80, T & 86, T & 104, T & 112, T & 120, _
T & 128, T & 136, T & 144, T & 152, T & 160, T & 168
If IsNumeric(TxBx.Text) Then
sngTxBx172Total = sngTxBx172Total + TxBx.Text
End If
End Select
Next TxBx
UserForm1.TextBox172.Text = sngTxBx172Total
End Sub

Ken Johnson