VBA userform_Get Value from additional textboxes to cells

[dotrongtrung]

Hi. I'm new to VBA and I dont know how to take action with the Userform here.

I have a userform which allows users add more textboxes and type the value to the textboxes. I want to copy those values from each additional textbox to separate cells. This is what I did, but not working.

"Add textbox after clicking_THIS ONE IS WORKING"
Private Sub CommandButton2_Click()


x = Me.Controls.count + 1
Set xx = Controls.Add("Forms.TextBox.1", "CtrlName" & x)
xx.Top = x * 20 - 108
xx.Left = 396
xx.Width = 288


End Sub




"Get value from textboxes to cell_THIS IS NOT WORKING"
Private Sub CommandButton1_Click()
Dim count as Integer
Dim i as Integer
count = Me.Controls.count - 9 ("I have 9 other controls, so need to - 9 to count the number of textboxes")
For i = 1 To count
Cells(i, 1).Select
ActiveCell.Value = Me.Controls("TextBox" & i).Value
Next i
End Sub


The problem is the Me.Controls("TextBox" & i).Value. I tried different funtions here, but still couldnt solve that. Can anyone help me with this. Thanks alot.

 

[Logit]

.
Taking a stab in the dark here ...

What about using"Forms" in that line:

 [I]"Forms.TextBox" & i [/I]

Here is some code (untested) and the link suggesting such:

[I]Sub AddTxtBoxAtRuntime()[/I][COLOR=#000000][FONT=Verdana]    [I]Dim i As Integer
    Dim oTxtBox As Control[/I]
    
    [I]For i = 1 To 5
        Set oTxtBox = Me.Controls.Add("Forms.TextBox.1")[/I]
        
        [I]With oTxtBox
            .Left = 5
            .Top = (.Height * (i - 1)) + (5 * i)
            .Text = "Hello-" & i
        End With
    Next i[/I]
    
    [I]Set oTxtBox = Nothing[/I][/FONT][/COLOR]
[I]End Sub[/I]

creating and textboxes in ecxcel vba during runtime | ExcelExperts.com

 

[mikerickson]

When you add the TextBoxes, that is a good time to store the cell location for their data in their .Tag property

Private Sub CommandButton2_Click()
    Dim x As Long
    x = Me.Controls.count + 1

    With Controls.Add("Forms.TextBox.1", "CtrlName" & x)
        .Top = x * 20 - 108
        .Left = 396
        .Width = 288
        .Tag = Cells(x, 1).Address(,,,True)
    End With
End Sub

Then when you go from the TextBox to the worksheet, you know where to put stuff

Private Sub CommandButton1_Click()
    Dim oneControl as Ms.Forms.Control

    For each oneControl in Me.Controls
        If oneControl.Tag Like "*!$*$*" Then
            Rem if the .Tag is a cell address 

            With oneControl
                Range(.Tag).Value = .Text
            End With

        End If
    Next oneControl
End Sub

 

[dotrongtrung]

Thanks a lot. I fixed my code, from Me.Controls("TextBox" & i).Value to Me.Controls("CtrlName" & (i + 9)).Value
Now it's working.

 

[Logit]

Good job !