VBA userform_Get Value from additional textboxes to cells

dotrongtrung

New Member
Joined
Jan 14, 2017
Messages
2
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,863
.
Taking a stab in the dark here ...

What about using"Forms" in that line:
Code:
 [I]"Forms.TextBox" & i [/I]

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

Code:
[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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
When you add the TextBoxes, that is a good time to store the cell location for their data in their .Tag property

Code:
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

Code:
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

New Member
Joined
Jan 14, 2017
Messages
2
Thanks a lot. I fixed my code, from Me.Controls("TextBox" & i).Value to Me.Controls("CtrlName" & (i + 9)).Value
Now it's working.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,330
Messages
5,601,000
Members
414,419
Latest member
JRDunya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top