Can I loop through Userform Textbox enter event in VBA?

Question

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

My feeling is that there must be a better way than 150 textboxes, but you would probably need a class module for this. — SJR, Mar 15 '22 at 12:32
Could you please explain what are you trying to achieve? I feel like you could benefit from the tag property to loop trough specific textboxes and extract `NumTest` from the ttxt name itself (I've noticed that `TextBox11` equals `NumTest=11` and so on...) — Foxfire And Burns And Burns, Mar 15 '22 at 12:59
Hi, thanks for the engaging with the question. The userform is made up of a matrix style input – size along the top and items along the left. When the user sets focus on a textbox - the +1, +5 & +10 buttons can be clicked to increment the quantity up by that number. I am trying the create a function that knows which textbox is active, to add the button to the correct textbox — Dellaa, Mar 15 '22 at 13:40

score 2 · Accepted Answer · answered Mar 15 '22 at 14:32

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

score 0 · Answer 2 · answered Mar 15 '22 at 14:05

0

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.

answered Mar 15 '22 at 14:05

Gove

1,745
10
11

Fyi *"...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"*: see EvR's [Reducing WithEvent declarations](https://stackoverflow.com/questions/61855925/reducing-withevent-declarations-and-subs-with-vba-and-activex/61893857#61893857), [Dynamic Textbox control Exit events](https://stackoverflow.com/questions/55737979/excel-userform-dynamic-textbox-control-exit-events/55738479#55738479) and Mathieu Guindon's *RubberDuck* article [Event propagation](https://rubberduckvba.wordpress.com/tag/events/) – T.M. Mar 20 '22 at 13:57
A work-around to simulate a Click event for dynamically added textboxes would be to profit from intermediate MouseDown/KeyDown events (with reduced KeyCodes for the latter) triggering a class procedure :-) – T.M. Mar 20 '22 at 14:08
Allow me a question: Were my last two comments helpful? - This recent answer might further help untangle unresponsive class events: [How to limit (textbox events) when a class control triggers](https://stackoverflow.com/questions/71539512/how-to-limit-when-a-class-control-triggers) – T.M. Mar 21 '22 at 18:01

Can I loop through Userform Textbox enter event in VBA?

2 Answers2