0

I found some vba code on the internet that dynamically creates a number of textboxes with a commandbutton click. For this a textbox and the button is allready placed on the Userform. I did create a second button that would repeat the proces (dynamically create a number of textboxes) but this button needs to get the value out of the dynamically, first, created textbox. Somehow I do not manage to call this textbox with my button click event. Can someone please help me out. Thank u so much

Private Sub cmdAddBoxes_Click()
Dim idx As Long
Dim maxBoxes As Long
Dim x As Long, y As Long
Dim ctl As Control
Dim newBox As MSForms.TextBox

maxBoxes = Val(txtNumBoxes.Text)
If (maxBoxes > 0) And (maxBoxes <= 1) Then
    ' remove any existing boxes
    For Each ctl In Me.Controls
        If Len(ctl.Tag) > 2 Then
            If Left(ctl.Tag, 3) = "new" Then
                Controls.Remove (ctl.Name)
            End If
        End If
    Next
    For idx = 1 To maxBoxes
        Set newBox = Me.Controls.Add("Forms.TextBox.1")
        With newBox
            .Tag = "new" & .Name
            .BackColor = &HC0FFFF
            .Value = 1
            'MsgBox "new" & .Name
            'MsgBox .Value
            ' make two columns of boxes
            If idx < 6 Then
                .Left = 12
                .Width = 78
                .Height = 18
                .Top = 16 + 24 * idx
            Else
                .Left = 100
                .Top = 16 + 24 * (idx - 5)
            End If
        End With
    Next
Else
    MsgBox "Number must be 1 to 1"
End If
txtNumBoxes.Text = ""
Label2.Visible = True

End Sub

Private Sub cmdAddBoxes1_Click()
Dim idx As Long
Dim maxBoxes As Long
Dim x As Long, y As Long
Dim ctl As Control
Dim newBox As MSForms.TextBox

maxBoxes = Val(NewTextBox1.Text)
If (maxBoxes > 0) And (maxBoxes <= 1) Then
    ' remove any existing boxes
    For Each ctl In Me.Controls
        If Len(ctl.Tag) > 2 Then
            If Left(ctl.Tag, 3) = "new" Then
                Controls.Remove (ctl.Name)
            End If
        End If
    Next

    For idx = 1 To maxBoxes
        Set newBox = Me.Controls.Add("Forms.TextBox.1")
        With newBox
            .Tag = "new" & .Name
            .BackColor = &HC0FFC0
            .Text = 1
            'MsgBox "new" & .Name
            ' make two columns of boxes
            If idx < 6 Then
                .Left = 12
                .Width = 78
                .Height = 18
                .Top = 16 + 24 * idx
            Else
                .Left = 100
                .Top = 16 + 24 * (idx - 5)
            End If
        End With
    Next
Else
    MsgBox "Number must be 1 to 1"
End If
NewTextBox1.Text = ""

End Sub

enter image description here

Dennis
  • 1
  • Why did you post the same code twice? the only difference I can see in both code segments is (1) the name (2) `txtNumBoxes` vs `NewTextBox1` (so, which one is it, we cannot know nor should we guess) (3) a different background color `&HC0FFFF` vs `&HC0FFC0` (4) using the `.Value` or the `.Text` property to change the value. So, none of these changes really matter too much and don't require a double-post of the same code snippet. – Ralph Mar 30 '17 at 13:40
  • reason I ask is that because in the sub cmdAddBoxes1_Click() the textbox named NewTextBox1.Text is created dynamically and I want to find out how to control this dynamically created textbox with a butoon click. but somehow when this line of code is executed maxBoxes = Val(NewTextBox1.Text) the value of maxBoxes = zero because the name of the dynamically created textbox is not recognized – Dennis Apr 03 '17 at 06:00
  • I am not sure if I understand you correctly. But it seems to me that you are trying to access the names of dynamically generated `TextBoxes` (during run-time). If that's the case then you should name your controls when you generate them. At the moment you are not naming them when you are adding them. To name them please have a look at this question / answer: http://stackoverflow.com/questions/10544456/dynamically-adding-labels-to-user-form-blank-userform Note, that the names are automatically increased by a number to ensure no conflicting names. – Ralph Apr 03 '17 at 09:27
  • Hi Ralph, Thanks for your answer, I added two lines of code. 1. With newBox .Name = "NewTextBox1" and 2. MsgBox .Name The result of the messagbox is NewTextBox1. So my conclusion is that the name is set but stil maxBoxes = Val(NewTextBox1.Text) the value of maxBoxes = zero. And i still got the error Run-time error '424': Object required. And yes I try to access the names of the dynamically generated textboxes but also trying to modify them – Dennis Apr 03 '17 at 10:24
  • To get the value from a control named `NewTextBox1` you'll have to use `Me.Controls("NewTextBox1").Value` as indicated here: http://stackoverflow.com/questions/24083717/referencing-excel-userform-control-name-value-from-string-vba – Ralph Apr 03 '17 at 11:14
  • It works. Thank u very much – Dennis Apr 04 '17 at 10:59
  • Problem solved. Thanks – Dennis Apr 06 '17 at 05:45

0 Answers0