Sum multiple userform textboxes after any are updated WITHOUT repeating same code for each texbox

[mwtinec]

I have built a fairly complex userform with multiple tabs (about 20 tabs). Each tab asks the user to enter values into several textboxes with a sum textbox on the tab. I have figured out how to use the afterupdate event to sum all the texbox values after any are changed and place the sum in the totals box.

Here's my current code for one afterupdate event.

Private Sub txtBOX1_AfterUpdate()​

txtSUM.Value = Int(txtBOX1.Value) + Int(txt.BOX2.Value) + Int(txtBOX3.Value)​

​

End Sub​

Problem is that this requires an afterupdate event for each box. By the time I do this there will be dozens of instances of basically the same code repeated over and over. Obviously, this gets to be very laborious to build and is prone to human error (my vba skills are novice at best).

Ideally what I'd like is to have one afterupdate event that watches all textboxes in a defined group. Then anytime updates are made to any textbox in that group then a macro is called that sums up all the values and places the total in the txtSUM box.

Originally I took the code above ( txtSUM.Value = Int(txtBOX1.Value) + Int(txt.BOX2.Value) + Int(txtBOX3.Value) and placed it in a module. I then called the module from the afterupdate event for each textbox. Much to my chagrin that didn't work. Like I said, my vba skills are weak.

In my research I found this thread over at Stackoverflow. User Roy uses a class module and userform module to do what I want structurally. He uses it to produce an msg box when someone enters a non-numeric value. I just want to add to that the summation of textboxes. I got his code to work with my userform so at least I know the basic solution works. Problem is that my vba skills aren't strong enough to modify for my need.

Any help would be greatly appreciated. Thanks in advance.

 

[BQardi]

Hi, I think the last post in that link is the easiest to understand if you are new to VBA and it would work for your purpose (I have also allowed myself to change your Int to CDbl to read decimal numbers correctly, Int only works with whole numbers. Change back if you only need Int):

Private Sub SumAll()
With Me.ActiveControl
    If Not IsNumeric(.Value) And .Value <> vbNullString Then
        MsgBox "Sorry, only numbers allowed"
        .Value = vbNullString
    [COLOR=#ff0000]Else[/COLOR]
        [COLOR=#ff0000]On Error Resume Next [/COLOR][COLOR=#008000]'The error check is only to ignore blank textboxes[/COLOR][COLOR=#ff0000]
        txtSUM.Value = [/COLOR][COLOR=#0000ff]CDbl[/COLOR][COLOR=#ff0000](txtBOX1.Value) + [/COLOR][COLOR=#0000ff]CDbl[/COLOR][COLOR=#ff0000](txtBOX2.Value) + [/COLOR][COLOR=#0000ff]CDbl[/COLOR][COLOR=#ff0000](txtBOX3.Value)
[/COLOR]    End If
End With
End Sub

Private Sub txtBOX1_AfterUpdate()
SumAll
End Sub

Private Sub txtBOX2_AfterUpdate()
SumAll
End Sub

Private Sub txtBOX3_AfterUpdate()
SumAll
End Sub

But for Rory's example you should be able to just add the same "Else -code" to the "If" statement (I haven't testet though):

Option Explicit
Private WithEvents tb As MSForms.TextBox
Public Property Set TextControl(t As MSForms.TextBox)
    Set tb = t
End Property
Private Sub tb_Change()

    With tb

        If Not IsNumeric(.Value) And .Value <> vbNullString Then

            MsgBox "Sorry, only numbers allowed"

            .Value = vbNullString
        [COLOR=#FF0000]Else[/COLOR]
            [COLOR=#ff0000]On Error Resume Next [/COLOR][COLOR=#008000]'The error check is only to ignore blank textboxes[/COLOR][COLOR=#ff0000]
            txtSUM.Value = [/COLOR][COLOR=#0000ff]CDbl[/COLOR][COLOR=#ff0000](txtBOX1.Value) + [/COLOR][COLOR=#0000ff]CDbl[/COLOR][COLOR=#ff0000](txtBOX2.Value) + [/COLOR][COLOR=#0000ff]CDbl[/COLOR][COLOR=#ff0000](txtBOX3.Value)[/COLOR]        End If

    End With

End Sub

I know there are better ways to do this, especially if you have A LOT of textboxes to SUM. A loop would be better for that purpose, something like the loop in rory's UserForm_Initialize code, but this is a little out of my scope I'm afraid...

 

[AddinBox_Tsunoda]

Hi, all.

You can easily program it if you use a general-purpose class module (clsBpca)
for Control-Array which I release (of course Enter/Exit events are available).
http://addinbox.sakura.ne.jp/Breakthrough_P-Ctrl_Arrays_Eng.htm
http://addinbox.sakura.ne.jp/Breakthrough_P-Ctrl_Arrays_Eng_ref.htm
Please try it.

clsBpca's Sample code
Using Exit event, if it is not a numeric, warning indication (TextBox)
& Using Enter / Exit event, ActiveControl indication
http://addinbox.sakura.ne.jp/Breakthrough_P-Ctrl_Arrays_Eng.htm#Chap5-1

  Private WithEvents NumBox As clsBpca

  Private Sub UserForm_Initialize()
      Set NumBox = New clsBpca    ' Create Instance
      With NumBox
          .Add txtNum1
          .Add txtNum2
          .Add txtNum3
          .Add txtNum4
          .Rgst  BPCA_EnterExit
      End With
  End Sub

  Private Sub UserForm_Terminate()
      NumBox.Clear
      Set NumBox = Nothing
  End Sub

  Private Sub NumBox_OnEnter(ByVal Index As Integer)
      NumBox.ItmTxt(Index).BackColor = &HFFFFE0    'LightCyan
  End Sub

  Private Sub NumBox_OnExit(ByVal Index As Integer, _
                        ByVal Cancel As MSForms.ReturnBoolean)
      If (NumBox.ItmTxt(Index).Value = "") Then
          'Empty is OK
      ElseIf IsNumeric(NumBox.ItmTxt(Index).Value) Then
          'Numeric is OK
      Else
          'Not Numeric is Error
          NumBox.ItmTxt(Index).BackColor = &HCCCCFF    'Light Red
          Beep
          Cancel = True
          Exit Sub
      End If

      NumBox.ItmTxt(Index).BackColor = vbWindowBackground
  End Sub

As all the explanation pages are poor English through the translation software,
will a meaning explaining reach you definitely?

 

[mwtinec]

I took the simpler route and that has done the trick. Thanks!

 

[BQardi]

You're welcome...