Update value of TextBox after other TextBoxes have updated

[Dark Sage]

I'm in the process of learning VBA and am working on a League Manager for my local billiard hall (The guy likes Excel). I have created a UserForm to enter scores and I'm looking for an easier way to code up my controls in it. I have a TextBox that needs to display the sum of values contained in other TextBoxes in the UserForm. Instead of writing an AfterUpdate subroutine that contains the same code for each TextBox, is there a way to just say
-Update this sum if any of the TextBoxes change or are updated
My research has pointed me in the direction of using a class for TextBoxes but what I've read is that the WithEvents modifier doesn't support Change or Before/After Update and I haven't been able to find an example that solves my problem. Or rather an example that works for only the TextBoxes I want to group, not ALL TextBoxes. As mentioned, I can easily write a duplicate sub routine (like below) for each of the TextBoxes but I feel there is a more efficient way.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Private Sub txtPlayer1_1_Score6_AfterUpdate()
txtPlayer1_1_Total.Value = CInt(txtPlayer1_1_Score1.Value) + _
CInt(txtPlayer1_1_Score2.Value) + _
CInt(txtPlayer1_1_Score3.Value) + _
CInt(txtPlayer1_1_Score4.Value) + _
CInt(txtPlayer1_1_Score5.Value) + _
CInt(txtPlayer1_1_Score6.Value)

End Sub</code>Any help would be appreciated. Thank you.

 

[dmt32]

Hi,
I am not familiar with scoring system used in Billiards & you do not say how many textboxes in total your userform contains.
If there are many (say 50 or more) then as far as I am aware, WithEvents controls may not support Exit and Enter and maybe some other events, but TextBox Change event should be available in a class.

If your form contains just a dozen or so then you could use a common procedure which is called by each of the controls change events

As an example for two players

Place in your userforms code page

'***************************************************************
'***************************PLAYER 1***************************


Private Sub txtPlayer1_1_Score1_Change()
    txtPlayer1_1_Total.Value = TotalScore(Me, 1)
End Sub


Private Sub txtPlayer1_1_Score2_Change()
    txtPlayer1_1_Total.Value = TotalScore(Me, 1)
End Sub


Private Sub txtPlayer1_1_Score3_Change()
    txtPlayer1_1_Total.Value = TotalScore(Me, 1)
End Sub


Private Sub txtPlayer1_1_Score4_Change()
    txtPlayer1_1_Total.Value = TotalScore(Me, 1)
End Sub


Private Sub txtPlayer1_1_Score5_Change()
    txtPlayer1_1_Total.Value = TotalScore(Me, 1)
End Sub


Private Sub txtPlayer1_1_Score6_Change()
    txtPlayer1_1_Total.Value = TotalScore(Me, 1)
End Sub


'***************************************************************
'***************************PLAYER 2***************************


Private Sub txtPlayer1_2_Score1_Change()
    txtPlayer1_2_Total.Value = TotalScore(Me, 2)
End Sub


Private Sub txtPlayer1_2_Score2_Change()
    txtPlayer1_2_Total.Value = TotalScore(Me, 2)
End Sub


Private Sub txtPlayer1_2_Score3_Change()
    txtPlayer1_2_Total.Value = TotalScore(Me, 2)
End Sub


Private Sub txtPlayer1_2_Score4_Change()
    txtPlayer1_2_Total.Value = TotalScore(Me, 2)
End Sub


Private Sub txtPlayer1_2_Score5_Change()
    txtPlayer1_2_Total.Value = TotalScore(Me, 2)
End Sub


Private Sub txtPlayer1_2_Score6_Change()
    txtPlayer1_2_Total.Value = TotalScore(Me, 2)
End Sub

Place Common code below in either your forms code page or standard module if you need to use it with another userform

 Function TotalScore(ByVal Form As Object, ByVal Player As Integer) As Integer    
   Dim i As Integer
    For i = 1 To 6
        TotalScore = TotalScore + Val(Form.Controls("txtPlayer1_" & Player & "_Score" & i).Value)
    Next
End Function

The common procedure has two parameters which you pass the UserForm object & the Player number.

The values of all 6 textboxes (based on your naming convention) will then be added together & shown in appropriate Total textbox.

This is just an idea but if want to share more detail of your project maybe another here will guide you to creating a class (I am off out for the day) if needed.

Dave

 

[Dark Sage]

Thank you for your reply Dave and apologies for how my code looked in the post (It looked fine in the preview). Using the common procedure would definitely be easier and more concise and may be what I end up doing. However my UserForm contains 36 of these score fields and it would be convenient to have the necessary TextBoxes related to eachother in such a way that when either of them update, the sum gets updated without having to write 36 Change() Events. My code so far is as follows.

Private Sub UserForm_Initialize()
    Dim teamRange As Range
    Dim playerRange As Range
    
    'Having an error handler for some reason shows a blank error box immediately after opening userform
    'On Error GoTo HandleError
    
    Set teamRange = ThisWorkbook.Sheets("PlayerList").Range("D2")
    Set playerRange = ThisWorkbook.Sheets("PlayerList").Range("A2")


    If IsEmpty(teamRange) Then
        MsgBox "There are no teams listed. Please add teams to the list"
        Set teamRange = Nothing
        Exit Sub
    End If


    If IsEmpty(playerRange) Then
        MsgBox "There are no players listed. Please add players to the list"
        Set playerRange = Nothing
        Exit Sub
    End If
    
    'Finds the next empty row and expands teamRange
    If Not IsEmpty(teamRange.Offset(1, 0)) Then
        Set teamRange = Range(teamRange, teamRange.End(xlDown))
    End If


    'Finds the next empty row and expands playerRange
    If Not IsEmpty(playerRange.Offset(1, 0)) Then
        Set playerRange = Range(playerRange, playerRange.End(xlDown))
    End If


    'The range's address is our rowsource
    cmbTeam1.RowSource = teamRange.Address(External:=True)
    cmbTeam2.RowSource = teamRange.Address(External:=True)


    'The range's address is our rowsource
    cmbPlayer1_1.RowSource = playerRange.Address(External:=True)
    cmbPlayer2_1.RowSource = playerRange.Address(External:=True)
    cmbPlayer3_1.RowSource = playerRange.Address(External:=True)
    cmbPlayer1_2.RowSource = playerRange.Address(External:=True)
    cmbPlayer2_2.RowSource = playerRange.Address(External:=True)
    cmbPlayer3_2.RowSource = playerRange.Address(External:=True)


'HandleError:
'    MsgBox Err.Description
End Sub


''For possible use later
'Private Sub cmbTeam1_Change()
'    Dim Arr As Variant
'    Arr = Application.Transpose(Sheets("PlayerList").Range("D2", Sheets("PlayerList").Cells(Rows.Count, "D").End(xlUp)))
'    If UBound(Arr) >= 0 Then
'        cmbTeam1.DropDown
'        cmbTeam1.List = Filter(Arr, cmbTeam1.Text, True, vbTextCompare)
'    Else
'        cmbTeam1.List = Arr
'    End If
'End Sub


'TODO: Figure out how to update after change of any control in a group so it's not just this last box
Private Sub txtPlayer1_1_Score6_AfterUpdate()
    txtPlayer1_1_Total.Value = CInt(txtPlayer1_1_Score1.Value) + _
                               CInt(txtPlayer1_1_Score2.Value) + _
                               CInt(txtPlayer1_1_Score3.Value) + _
                               CInt(txtPlayer1_1_Score4.Value) + _
                               CInt(txtPlayer1_1_Score5.Value) + _
                               CInt(txtPlayer1_1_Score6.Value)


End Sub


Private Sub txtPlayer2_1_Score6_AfterUpdate()
    txtPlayer2_1_Total.Value = CInt(txtPlayer2_1_Score1.Value) + _
                               CInt(txtPlayer2_1_Score2.Value) + _
                               CInt(txtPlayer2_1_Score3.Value) + _
                               CInt(txtPlayer2_1_Score4.Value) + _
                               CInt(txtPlayer2_1_Score5.Value) + _
                               CInt(txtPlayer2_1_Score6.Value)
End Sub


Private Sub txtPlayer3_1_Score6_AfterUpdate()
    txtPlayer3_1_Total.Value = CInt(txtPlayer3_1_Score1.Value) + _
                               CInt(txtPlayer3_1_Score2.Value) + _
                               CInt(txtPlayer3_1_Score3.Value) + _
                               CInt(txtPlayer3_1_Score4.Value) + _
                               CInt(txtPlayer3_1_Score5.Value) + _
                               CInt(txtPlayer3_1_Score6.Value)
End Sub


Private Sub AddScores_Click()
    'Do more later
    Unload Me
End Sub