Can I loop through Userform Textbox enter event in VBA?-CodePudding

I have a userform with over 150 textboxs (numbers) and want to be able to add to it with the plus and minus buttons shown.

I have hard coded each textbox enter event and feel there has to be a more straightforward way.

Can I loop through these textbox events?

Userform with 150 textboxes

Dim NumTest As Integer


Private Sub TextBox1_Enter()
    NumTest = 1
End Sub
Private Sub TextBox2_Enter()
    NumTest = 2
End Sub
Private Sub TextBox3_Enter()
    NumTest = 3
End Sub
Private Sub TextBox4_Enter()
    NumTest = 4
End Sub
Private Sub TextBox5_Enter()
    NumTest = 5
End Sub
Private Sub TextBox6_Enter()
    NumTest = 6
End Sub
Private Sub TextBox7_Enter()
    NumTest = 7
End Sub
Private Sub TextBox8_Enter()
    NumTest = 8
End Sub
Private Sub TextBox9_Enter()
    NumTest = 9
End Sub
Private Sub TextBox10_Enter()
    NumTest = 10
End Sub
Private Sub TextBox11_Enter()
    NumTest = 11
End Sub
Private Sub TextBox12_Enter()
    NumTest = 12
End Sub
'And so on....

I have the below to be able to add the


'*********************ADD/SUBTRACT BUTTONS ******************************
Private Sub CommandButtonMOne_Click()
    Me("TextBox" & NumTest).Value = Me("TextBox" & NumTest).Value - 1
End Sub
Private Sub CommandButtonMFiv_Click()
    Me("TextBox" & NumTest).Value = Me("TextBox" & NumTest).Value - 5
End Sub

Private Sub CommandButtonMTen_Click()
    Me("TextBox" & NumTest).Value = Me("TextBox" & NumTest).Value - 10
End Sub
Private Sub CommandButtonPOne_Click()
    Me("TextBox" & NumTest).Value = Me("TextBox" & NumTest).Value   1
End Sub
Private Sub CommandButtonPFiv_Click()
    Me("TextBox" & NumTest).Value = Me("TextBox" & NumTest).Value   5
End Sub
Private Sub CommandButtonPTen_Click()
    Me("TextBox" & NumTest).Value = Me("TextBox" & NumTest).Value   10
End Sub

CodePudding user response：

Because you only need to change the value of the most recently selected textbox, you could use the form's ActiveControl property to tell which textbox is selected. This would require no code on the textboxes. However, because buttons get the focus when you click them, you can't use this approach with a button. You could change your buttons to labels and write the code on the label's "click" event. labels will work because they don't get the focus when they are clicked.

The code for your "Plus 5 label" would look like this:

ActiveControl.Value = ActiveControl.Value   5

This Approach will work for even if the textboxes are created at runtime. This code will have an error if no textbox is selected, so you could add an if statement to check as follows:

Private Sub lblAddFive_Click()
  If TypeName(ActiveControl) = "TextBox" Then
    ActiveControl.Value = ActiveControl.Value   5
  Else
    MsgBox "Selecte a textbox first."
  End If
End Sub

CodePudding user response：

If the number of textboxes needs to change, this is a challenge because while you can dynamically add textboxes and attach code to their "change" event, I don't know of a way to attach to the "enter" or "click" event. If you don't need to change the number of textboxes at runtime, I recommend writing a loop that produces the VBA code for the texboxes and just paste it into your module. Something like the following:

Sub write_code()
 Dim x As Integer
 For x = 1 To 150
  Debug.Print "Private Sub TextBox" & x & "_Enter():";
  Debug.Print "NumTest = " & x & ":End Sub"
  Next
End Sub

I've written the code to produce the event sub-procedures on a single line because you need 150 lines and the debug window is limited to 200 lines total.

I could give more thought to how to do this if the number of textboxes changes at runtime. So if that is the case, please comment below.