Help with Dynamic Text Boxes

Question

Hi,

In a form with the help of a "Add" button 4 textbox will be created and when we press the add button again this will create another 4 textbox below the previous one. I am getting this correctly.

But I am not sure how I will save the values which is entered in all the text box to an excel sheet when we click on a submit button.

Also is it possible to create a new set of textbox created below automatically when a user input any value in the 4th textbox? (this will help the user not to click on the "Add" button everytime.

Below is the code which I use to generate the 4 text boxes.

Sub btnAdd_Click()

    Static i As Integer

     Dim txtbox, txtbox2, txtbox3, txtbox4 As Object

     Set txtbox = UserForm2.Controls.Add("Forms.TextBox.1")

     Set txtbox2 = UserForm2.Controls.Add("Forms.TextBox.1")

     Set txtbox3 = UserForm2.Controls.Add("Forms.TextBox.1")

     Set txtbox4 = UserForm2.Controls.Add("Forms.TextBox.1")

    i = i + 1

        With txtbox

            .Name = "checkName" & i

            .Left = 10

            .Height = 20

            .Top = 20 + (25 * i)

        End With

        With txtbox2

            .Name = "checkName2" & i

            .Left = 90

            .Height = 20

            .Top = 20 + (25 * i)

        End With

        With txtbox3

            .Name = "checkName3" & i

            .Left = 170

            .Height = 20

            .Top = 20 + (25 * i)

        End With

        With txtbox4

            .Name = "checkName4" & i

            .Left = 250

            .Height = 20

            .Top = 20 + (25 * i)

        End With

End Sub

Can anyone please help me? Thank you in advance.

Answer 1

Are you able to share a copy of the sheet and I can show an example, it is quite difficult to explain by description?

Kind Regards,

Elise

Sure,

Please find the below link to download the file.

https://1drv.ms/x/s!AkcJ58b\_Aoqh1mms6ZaKh9ITK4t8?e=cjJva8

Answer 2

Are you able to share a copy of the sheet and I can show an example, it is quite difficult to explain by description?

Kind Regards,
Elise

Answer 3

Hi Elise,

Thank you for the reply.

I tried the below but nothing happens. Can you please explain a bit...

Sub TextBox_Change()

Range("A1").Value = TextBox1.Value

'Code for to add new textbox goes here

End SubAlso when we click on the Add button it will create another set of text boxes below.In below image you can see that are 20 textbox (5 Rows x 4 Column)So when I click on Submit the values must be pasted in a sheet Range("A1:D5")

Answer 4

That should have read:

Range("A1").Value = TextBox1.Value

Answer 5

Hi, I'm Elise, an independent advisor and I'd be happy to help with your issue.

You would need to make use of the TextChange event.

Create a new method for example:

Sub TextBox_Change()

Range("A").Value = TextBox1.Value

‘Code for to add new textbox goes here

End Sub

The when you create a textbox add this:

AddHandler TextBox1.Change, AddressOf TextBox_Change

Within the method you can then set rules for how you want to add the textbox and again assign the event handler to it to create another if it changes.

Please let me know if you need any further assistance.

Kind Regards,

Elise