Share via

Macro to Insert new text box into userform

Anonymous
2011-06-17T13:11:57+00:00

I have a userform where a user will enter items on to a list.  I originally had 20 list boxes with a named range serving the list box.  Well my named range had a list of about 500 items but I recently aquired a new data set that has 2000 items.  My new plan is to dynamicly add text boxes when a user clicks a button labeled "Add Item"  What I can't figure out is how to add the text box, name the next number (ie textbox1, textbox2, textbox3...........) and place it under the previous textbox.

Thanks in advance for any help you guys can offer me.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-06-20T15:29:06+00:00

Try it this way.

Bernie

Option Explicit

Public myTop As Integer

Private Sub CommandButton1_Click()

Dim cCntrl As Control

Dim myCount As Integer

myCount = Me.Controls.Count

    Set cCntrl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & myCount + 1, True)

    With cCntrl

        .Width = 150

        .Height = 25

        myTop = myTop + 35

        .Top = myTop

        .Left = 10

    End With

End Sub

Was this answer helpful?

1 person found this answer helpful.
0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-06-20T16:39:27+00:00

    Just add in the frame object, like this

    myCount = Me.Frame1.Controls.Count

    mytop = myCount * 14.6

        Set item = Me.Frame1.Controls.Add("Forms.Combobox.1", "Item" & myCount + 1, True)

    Bernie

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-06-20T16:27:47+00:00

    Okay Bernie bear with me I modified your code to suit my needs

    Private Sub CommandButton1_Click()

    Dim mytop As Integer

    Dim item As Control

    Dim myCount As Integer

    myCount = Me.Controls.Count

    mytop = myCount * 14.6

        Set item = Me.Controls.Add("Forms.Combobox.1", "Item" & myCount + 1, True)

        With item

            .TabIndex = myCount + 1

            .RowSource = "item"

            .Width = 150

            .Height = 14.5

            .top = mytop

            .Left = 10

            .Value = "Item" & myCount + 1

        End With

    End Sub

    What I am wondering now is on the placment of the text box, how would a place it in a frame instead of just a location in relation to the form

    Oh yeah I added the ".Value = "Item" & myCount + 1" in building this just to be sure that I am getting the name that I want in my code.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-06-20T12:26:27+00:00

    Thanks Bernie, but the logic/storage of varible value is the part I am having trouble with.

    and also in your code to name the textbox would I just add a ".Name=" to the "With cCntrl"

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-06-17T14:09:55+00:00

    Basic code:

    Dim cCntrl As Control

        Set cCntrl = Me.Controls.Add("Forms.TextBox.1", "MyTextBox", True)

        With cCntrl

            .Width = 150

            .Height = 25

            .Top = 10

            .Left = 10

        End With

    But you need some logic / storage of variable values to determine where to place it and what name to use.

    Bernie

    Was this answer helpful?

    0 comments