Create a single handler for multiple textboxes

[Bruce54]

I have a UserForm with a large number of similarly named textboxes. A DARTS Scoring System.
In fact, I've created a userform that mostly duplicates the SpreadSheet function

Using a spreadsheet is, I know, by far the best for this, but I was curious to see
if it was not only feasible but primarily useful. Situationally, giving a half-inebriated group of players
control of a complex program like Excel, is inviting all sorts of disasters.
Recreating it in UserForm style eliminates most, if not all of their ability to "fiddle", so to speak.
At the moment my fully working solution is as follows:

Private Sub TextBox1_AfterUpdate()
    TEAMSSum
End Sub


Private Sub TextBox2_AfterUpdate()
    TEAMSSum
End Sub
. . . . . .(same pattern up to ...)
 
Private Sub TextBox72_AfterUpdate()
    TEAMSSum
End Sub

As you can see from the code, I've used a single handler for each Control (TextBox) clicked.
the 72 controls indicated are but the first of 8 groups of such TextBoxes.

At the moment, they only handle a single event, the AfterUpdate
Ideally, I need 3 events (OnFocus / AfterUpdate / LostFocus):
OnFocus: Change BackColor/ForeColor - (Highlights the ActiveControl)
AfterUpdate: this is handled by the TEAMSSum Function)
LostFocus: Revert BackColor/ForeColor - (de-Highlights the ActiveControl)

What I was hoping for was a single routine that could handle all of these controls
or, alternatively, a best-practice method to handle them.
Perhaps something like:

   With ActiveControl
       .ForeColor = vbYellow
       .BackColor = vbRed
   End With

TEAMSSum (mostly formatting and calculations) calls a Function that accepts only Numeric and decimal input.


   With ActiveControl
       .ForeColor = vbBlack
       .BackColor = vbWhite
   End With

the Function TEAMSSum could be used as a container for the proposed routine, as all the Subs call it. (it sums the values of the textboxes)
so the flow would be something like IdentifyActiveControl>HighlightActiveControl>UseInput>De-HighlightActiveControl>IdentifyNextActiveControl ...

Any and all suggestions, help or advice welcomed
In fact, thank you, in advance, for even reading this post

Bruce54

 

[PatOBrien198]

Perhaps something like... (for 3 text boxes):
(Using the text box exit event for the TeamSum call)

Sub EnterColor()
    With ActiveControl
        .ForeColor = vbYellow
        .BackColor = vbRed
    End With
End Sub

Sub ExitColor()
    With ActiveControl
        .ForeColor = vbBlack
        .BackColor = vbWhite
    End With
    
    TeamSum
    
End Sub

Sub TeamSum()
    MsgBox "Team sum routine..."
    
End Sub

Private Sub TextBox1_Enter()
    EnterColor
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitColor
End Sub

Private Sub TextBox2_Enter()
    EnterColor
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitColor
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitColor
End Sub

Private Sub TextBox3_Enter()
    EnterColor
End Sub

 

[Bruce54]

Perhaps something like... (for 3 text boxes):
(Using the text box exit event for the TeamSum call)

Sub EnterColor()
    With ActiveControl
        .ForeColor = vbYellow
        .BackColor = vbRed
    End With
End Sub

Sub ExitColor()
    With ActiveControl
        .ForeColor = vbBlack
        .BackColor = vbWhite
    End With
    
    TeamSum
    
End Sub

Sub TeamSum()
    MsgBox "Team sum routine..."
    
End Sub

Private Sub TextBox1_Enter()
    EnterColor
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitColor
End Sub

Private Sub TextBox2_Enter()
    EnterColor
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitColor
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitColor
End Sub

Private Sub TextBox3_Enter()
    EnterColor
End Sub

Thanks for the Reply Pat.
I can see where you are going with that. It has merit ... but ...I was asking if it was possible to have a single handler for ALL the TextBox#_Click events.

One that perhaps picks up WHICH control was clicked, highlight it, accept the input, de-highlight it, move on in sequence.

Unfortunately, after viewing many explanations, it would seem I'm chasing a "Chicken or the Egg" problem

Yours is so far the better solution (although to be truthful, there's only yours and mine, so far)

Again, many thanks for your attention and efforts

They are appreciated

Bruce54

 

[Jon Peltier]

Pass the control into the subs that you call:

Sub TeamSum(myTextBox As Control)

  ' blah blah

End Sub

Sub EnterColor(myTextBox As Control)
    With myTextBox
        .ForeColor = vbYellow
        .BackColor = vbRed
    End With
End Sub

Sub ExitColor(myTextBox As Control)
    With myTextBox
        .ForeColor = vbBlack
        .BackColor = vbWhite
    End With
    
    TeamSum myTextBox
    
End Sub

Private Sub TextBox1_Enter()
    EnterColor TextBox1
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitColor TextBox1
End Sub

Private Sub TextBox2_Enter()
    EnterColor TextBox2
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitColor TextBox2
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ExitColor TextBox3
End Sub

Private Sub TextBox3_Enter()
    EnterColor TextBox
End Sub

 

[Bruce54]

Thanks Pat,
I've taken a copy of both answers and the coding, that looks like enough to do the job.

Many Thanks, this can close now.

Bruce 54