Calculate Userform Text box from other text boxes

[ehayman2003]

My apologies as i have not used userforms much.

I have a userform and i am trying to total textboxes 1-18 in textbox 19, automatically:

I have the following code:

Public Sub recalculatetotal()
'calculates total score in textbox 19
Dim Tot As Double
On Error Resume Next
Tot = Tot + CDbl(TextBox1.Text)
Tot = Tot + CDbl(TextBox2.Text)
Tot = Tot + CDbl(TextBox3.Text)
Tot = Tot + CDbl(TextBox4.Text)
Tot = Tot + CDbl(TextBox5.Text)
Tot = Tot + CDbl(TextBox6.Text)
Tot = Tot + CDbl(TextBox7.Text)
Tot = Tot + CDbl(TextBox8.Text)
Tot = Tot + CDbl(TextBox9.Text)
Tot = Tot + CDbl(TextBox10.Text)
Tot = Tot + CDbl(TextBox11.Text)
Tot = Tot + CDbl(TextBox12.Text)
Tot = Tot + CDbl(TextBox13.Text)
Tot = Tot + CDbl(TextBox14.Text)
Tot = Tot + CDbl(TextBox15.Text)
Tot = Tot + CDbl(TextBox16.Text)
Tot = Tot + CDbl(TextBox17.Text)
Tot = Tot + CDbl(TextBox18.Text)
TextBox19.Text = Tot
End Sub
Private Sub TextBox19_Change()
Call recalculatetotal

When I open the form and enter data in each text box, box 19 does not populate.

Additionally, this code is in the general section of the module.

any help is greatly appreciated.

thanks in advance.

 

[mikerickson]

That routine would have to be called by event code for each of Textbox1 thru 18. It could be in the Change event, the AfterUpdate or BeforeUpdate events, but it would have to be in one event per textbox (for those 18).

 

[ehayman2003]

That routine would have to be called by event code for each of Textbox1 thru 18. It could be in the Change event, the AfterUpdate or BeforeUpdate events, but it would have to be in one event per textbox (for those 18).

MRExcelMVP -

Thank you. That sounds rather cumbersome... is there a method to make this easier?

Basically, i want the Textbox 19 to update as i enter values into boxes 1-18.

thanks for your help...

 

[DanteAmor]

MRExcelMVP -

Thank you. That sounds rather cumbersome... is there a method to make this easier?

Basically, i want the Textbox 19 to update as i enter values into boxes 1-18.

thanks for your help...

It can be done with a Class

Put the following code in your user form

Dim colTbxs As Collection 'Collection Of Custom Textboxes


Private Sub UserForm_Initialize()
    Dim ctlLoop As MSForms.Control
    Dim clsObject As Class1
    Set colTbxs = New Collection
    For Each ctlLoop In Me.Controls
        If TypeOf ctlLoop Is MSForms.TextBox Then
            Set clsObject = New Class1
            Set clsObject.tbxCustom1 = ctlLoop
            colTbxs.Add clsObject
        End If
    Next ctlLoop
End Sub


Sub recalculatetotal()
    'calculates total score in textbox 19
    Dim Tot As Double
    On Error Resume Next
    For i = 1 To 18
        Tot = Tot + CDbl(Me.Controls("TextBox" & i).Value)
    Next
    TextBox19.Text = Tot
End Sub

Create the class: Class1
Menu VBA, Insert, Class
Put the following code inside the Class1

Public WithEvents tbxCustom1 As MSForms.TextBox 'Custom Textbox
Private Sub tbxCustom1_Change()
    Call UserForm1.recalculatetotal
End Sub

If you capture a number in any of the 18 textbox, the total will be updated in the textbox 19.

 

[DanteAmor]

You can simplify the loading of the class for the 18 textbox in this way:

Dim colTbxs As Collection 'Collection Of Custom Textboxes


Private Sub UserForm_Initialize()
    Dim clsObject As Class1, i as Long
    Set colTbxs = New Collection
[COLOR=#0000ff]    For i = 1 To 18[/COLOR]
[COLOR=#0000ff]        Set clsObject = New Class1[/COLOR]
[COLOR=#0000ff]        Set clsObject.tbxCustom1 = Me.Controls("TextBox" & i)[/COLOR]
[COLOR=#0000ff]        colTbxs.Add clsObject[/COLOR]
[COLOR=#0000ff]    Next[/COLOR]
End Sub


Sub recalculatetotal()
    'calculates total score in textbox 19
    Dim Tot As Double, i as Long
    On Error Resume Next
    For i = 1 To 18
        Tot = Tot + CDbl(Me.Controls("TextBox" & i).Value)
    Next
    TextBox19.Text = Tot
End Sub

In module class:

Public WithEvents tbxCustom1 As MSForms.TextBox 'Custom Textbox
Private Sub tbxCustom1_Change()
    Call UserForm1.recalculatetotal
End Sub