Addition code for 110 TextBoxes

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

The below code adds up all the values of the specified TextBoxes. I need
this code to run after any value in any of the specified textboxes change in
addition to another 88 TextBoxes that I have not listed. Instead of having
to put this code in all 110 TextBoxes, is there an easier way to get this
code to run?

TextBox9.Value TextBox14.Value + TextBox19.Value + TextBox24.Value +
TextBox29.Value + TextBox34.Value + _
TextBox39.Value + TextBox44.Value + TextBox49.Value + TextBox54.Value +
TextBox59.Value + TextBox64.Value + _
TextBox69.Value + TextBox74.Value + TextBox79.Value + TextBox84.Value +
TextBox89.Value + TextBox94.Value + _
TextBox99.Value + TextBox104.Value + TextBox109.Value + TextBox114


Thank you

Todd Huttenstine
 
Use John Walkenbach's method

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine


although written for comandbuttons, it can be adapted to your situation I
would think.

Another solution might be to link your textboxes to cells in a worksheet and
have a sum function get the value of all the cells.
 
Todd,

Another option..................
This would be easier if what you wanted to add were already
sequentially numbered.

If it's possible to rename the TextBoxes from let's say
TextBox1 to TB1, TB2, etc.
I'm sure this probably isn't feasable if you already have code linked
to the TextBoxes, but for future reference.....................

You could then use a loop to add them up:

Example:

Sub TestMe()
Dim x As Integer
Dim mysum As Integer
mysum = 0
On Error Resume Next
For x = 1 To 110
mysum = mysum + Val(UserForm1.Controls("TB" & x).Value)
Next x
On Error GoTo 0
MsgBox mysum
End Sub

John
 
Thank you, but I cannot get it to work for my textbox issue. I used the
following code...


MODULE1 CODE:

Option Explicit
Dim Text1() As New Class1

Sub ShowDialog()
'ButtonCount is now TextCount
'Buttons is now Text1
Dim TextCount As Integer
Dim ctl As Control

TextCount = 0
For Each ctl In UserForm1.Controls
If TypeName(ctl) = "TextBox" Then
If ctl.Name <> "OKButton" Then 'Skip the OKButton
TextCount = TextCount + 1
ReDim Preserve Text1(1 To TextCount)
Set Text1(TextCount).ButtonGroup = ctl
End If
End If
Next ctl
UserForm1.Show
End Sub

CLASS1 CODE:
Public WithEvents ButtonGroup As TextBox

Private Sub ButtonGroup_Click()
MsgBox "Hello from " & ButtonGroup.Name
End Sub

WHen I run it, I get the error "Object does not source automation events"

It highlights this bit of code
Public WithEvents ButtonGroup As TextBox
 
Oh that is very helpful. Thanx. Yeah I cannot do that now, but for future
its nice.

Todd Huttenstine
 
Tom,

Seems that I missed that part about Todd wanting the code
to fire whenever *any* one of the TextBoxes changed.
Yes, you would need 110 event macros.

Wouldn't be all that hard to do though if you just called
a sub within each of them and the sub did the calculation.
I have, in the past, copied and pasted many and then
just went along and changed the references on each of them.
Gets the job done, but not the best method.

Your suggestion to link them all to cells with a sum function
would work too, but you'd still have to go back and create the
links. Would probably be just as time consuming as the
100 events.

Personally, I'd just have a button call the macro when you
wanted to do the calculation.

John
 
I set the type to MSforms.Textbox. Here is my new code.


MODULE1:
Option Explicit
Dim Boxes() As New Class1

Sub ShowDialog()
'Buttons to Boxes
'ButtonCount to TextCount
'ButtonGroup to TextGroup

Dim TextCount As Integer
Dim ctl As Control

' Create the Button objects
TextCount = 0
For Each ctl In UserForm1.Controls
'If TypeName(ctl) = "MSforms.Textbox" Then
'If ctl.Name <> "OKButton" Then
TextCount = TextCount + 1
ReDim Preserve Boxes(1 To TextCount)
Set Boxes(TextCount).ButtonGroup = ctl
'End If
'End If
Next ctl
UserForm1.Show
End Sub


CLASS1:
Public WithEvents ButtonGroup As MSforms.TextBox

Private Sub ButtonGroup_Click()
MsgBox "Hello from " & ButtonGroup.Name
End Sub

Now the form pops up but nothing happens when I click any textbox.
 
Back
Top