How do I update value of dynamically created textbox when value of another dynamically created textbox (on the same Userform) changes

Question

I have an Excel Userform that includes dynamically created textboxes. I would like to change he value of one textbox if the value of another textbox changes.

Here is my code vba code:

Sub UserForm_Initialize()
Dim cTextBox As Control
Dim totalfig As Integer
Dim a As String
Dim b As Integer

Set cntrls = New Collection

Set cTextBox = Controls.Add("Forms.TextBox.1", "HorseName")
cTextBox.Height = 25
cTextBox.Width = 400
cTextBox.Top = 5
cTextBox.Left = 30
cTextBox.Value = "Widgets"
cTextBox.Locked = True
cTextBox.Font.Size = 15
cTextBox.SpecialEffect = 0
cTextBox.BackColor = &H8000000F
cntrls.Add cTextBox, cTextBox.Name

For i = 1 To 2
a = "20 Big Widgets"
b = 20

Set cTextBox = Controls.Add("Forms.TextBox.1", "Product_" & i)
cTextBox.Height = 18
cTextBox.Width = 100
cTextBox.Top = 36 + (22 * i)
cTextBox.Left = 36
cTextBox.Value = a
cTextBox.BackColor = &H8000000F
cntrls.Add cTextBox, cTextBox.Name


Set cTextBox = Controls.Add("Forms.TextBox.1", "Units_" & i)
cTextBox.Height = 18
cTextBox.Width = 50
cTextBox.Top = 36 + (22 * i)
cTextBox.Left = 138
cTextBox.Value = b
cTextBox.BackColor = &H8000000F
cntrls.Add cTextBox, cTextBox.Name
Next i
End Sub

Here is the output:

Now, assuming the user changes the first "20 Big Widgets" to "30 Big Widgets" I would like the second column (in the first row) that contains the number of widgets to automatically change from 20 to 30. Like so:

I understand how to accomplish this if I use a static textbox, but how do you accomplish this with a dynamic textbox.

Thanks in advance for your help.

Answers to similar question are here: https://stackoverflow.com/questions/5940596/excel-vba-userform-execute-sub-when-something-changes — TomJohn, Feb 11 '18 at 20:22
Another similar question here https://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically — Storax, Feb 11 '18 at 20:35
Possible duplicate of [Assign code to a button created dynamically](https://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically) — Excel Developers, Feb 12 '18 at 11:40

score 0 · Answer 1 · edited Nov 04 '18 at 16:48

You've assigned a fix value to variable b. Try changing it as per the value of the first textbox. Add this line.

b=WorksheetFunction.Left(a,Search(" ",a)-1)

Now, I haven't tried it myself but it should work. But if it doesn't, just pull the value of "a" from a cell and then apply the same formula mentioned above in the cell next to it and pull the value for b from that one.

Let me know if any queries.

How do I update value of dynamically created textbox when value of another dynamically created textbox (on the same Userform) changes

1 Answers1