Auto-Update Userform Label when text boxes change values

[tryingmybest418]

Hi all,

I have a userform with 18 textboxes, all set up to only accept numbers.

I have a label that is set up to display the sum of the 18 textboxes, and have the textboxes and label set up to read any empty text boxes as "0".

I am looking for a way to have the label automatically update when any of the text box values changes. Is this doable?

Thanks!

 

[My Aswer Is This]

Show us the code you are now using.

 

[dmt32]

Hi,
what you want should be doable

One way would be to create a common code for each of your textbox change events to call


Place in your forms code page:

Private Sub SumTextBoxes()
        Dim Ctl As Control
        Dim Total As Double
        For Each Ctl In Me.Controls
              If TypeName(Ctl) = "TextBox" Then Total = Total + Val(Ctl.Value)
        Next Ctl
        
        Me.Label1.Caption = Total
End Sub

Change your forms Label name shown in RED as required.

Each of your TextBoxes will require a change event to call the procedure

Private Sub TextBox1_Change()
    SumTextBoxes
End Sub


Private Sub TextBox2_Change()
    SumTextBoxes
End Sub


Private Sub TextBox3_Change()
    SumTextBoxes
End Sub


Private Sub TextBox4_Change()
    SumTextBoxes
End Sub

'etc
'etc

a less busy way would be to create a Class for the Textboxes which would then only require one change event. But see how you get on with above suggestion first.


Dave

 

[tryingmybest418]

This is brilliant, thanks so much. It's working grat.

Now I do have two other text boxes on the form that are also numbers, but I don't want to include them in the sum, would there be a way to do that?

I've named those "txtSlopeRating" and "txtCourseRating".
While the text boxes I want to sum are all labeled "txtParHole1", "txtParHole2" ...etc. If there's a way to reference that?

Thanks again!

 

[dmt32]

Hi,
try this update

Private Sub SumTextBoxes()
    Dim Total As Double
    Dim i As Integer
    For i = 1 To 18
        Total = Total + Val(Me.Controls("txtParHole" & i).Value)
    Next i
    Me.Label1.Caption = Total
End Sub

This assumes that your textboxes are named "txtParHole1", "txtParHole2" through to "txtParHole18"

Dave

 

[tryingmybest418]

It's working like a dream, thanks again for all the help!

 

[tryingmybest418]

Oh, you mentioned using a "class" that would not require 18 different change events. How would that work for this?

 

[dmt32]

Oh, you mentioned using a "class" that would not require 18 different change events. How would that work for this?

Hi, Try this.

1 – make a backup of your workbook

2 – In your Usefrorm, DELETE ALL the change events & the SumTextBoxes codes

2-1 Place following in your Userforms code page

 Private Sub UserForm_Initialize()    
    Dim i As Integer
    ReDim TextBox(1 To 18)
    For i = 1 To 18
        Set TextBox(i).TextBoxClass = Me.Controls("txtParHole" & i)
    Next
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Set TextBoxClass = Nothing
End Sub

3 – From VBA editor Menu Insert >Module

• Place all following code

 Public TextBox() As New TextBoxClass

Sub SumTextBoxes(ByVal Form As Object)
    Dim Total As Double
    Dim Box As Variant
    For Each Box In TextBox
        Total = Total + Val(Box.TextBoxClass.Value)
    Next
    Form.Label1.Caption = Total
End Sub

Change only the Label name shown in RED as required.
Note the Public Declaration which MUST sit at the very TOP of the Module OUTSIDE any procedure.

4 – From VBA editor Menu Insert > Class Module

• Name the Module TextBoxClass << Very Important that you do this

• Place all following code

 Public WithEvents TextBoxClass As MSForms.TextBox

Private Sub TextBoxClass_Change()
    SumTextBoxes TextBoxClass.Parent
End Sub

Note the Public Declaration which MUST sit at the very TOP of the Class Module OUTSIDE any procedure.

Close the editor & run your form, which should sum the entered values.
Just a mention you can if needed, add a single key press event in the class to call a function to limit numeric values only being entered.

Hopefully, all will work as required but I am not around much this week - if you have issues, you can always revert to your backup copy.

Dave

 

[tryingmybest418]

Sorry for the delay - I've been away and haven't revisited this.

Thank you so much for the information. I'll look through this and try it out.

I appreciate all the help!