Dynamic Userform Linking

[bahllr]

I have a userform with code that allows a user to specify "N " number (1-20) in a textbox which then creates "N" textboxes as specified by the user.

I would like to link those textboxes (the ones created by the code) to cells on the worksheet but don't know how.

I would like the value of the first textbox created to link to BA3, second box linking to BB3, 3rd linking to BC3 and so on (up to 20 possible linkages)

But when the userform is displayed in edit mode, none of the textboxes are shown for me to click on and type code! I don't know how to solve this....

any ideas?

 

[ChrisM]

When you create each textbox, add one line right afterwards:

myTextBox1.ControlSource = "Sheet1!BA3"

the control source works both ways, when you load the userform it will first show what is in BA3, and when you change the textbox value then cell BA3 will automatically change.

 

[timorrill]

The following code seems to accomplish what you are looking for as well:

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim txtBox As Object
    
    For i = 1 To txtCount.Value
        Set txtBox = Me.Controls.Add("Forms.TextBox.1")
        txtBox.Top = i * 20 + 50
        txtBox.Left = 20
        txtBox.ControlSource = Worksheets("Sheet1").Cells(3, i + 52).Address
    Next i
End Sub

 

[bahllr]

TiMorrill,

This looks very close. I tested the code but "txtCount" was an undefined value that it didn't recognize.

Could you help me out with that? (Sorry...beginner to VBA

 

[timorrill]

txtCount was the name of the text box on the userform that the user types the number of text boxes into. This is how I am letting the user specify "N" number of text boxes on the userform.

 

[bahllr]

Timorrill,

Thanks for the help!. I was able to integrate my code with the last line of code on your example (controlsource). This works perfectly!