How to set textbox name as variable and allow to alter the name in loop?

Question

I know that the title is unclear .Hence , i try to explain in detail in the content .

There are 15 textbox in the VB interface as picture below .

This is a user interface that allows user to input the data and store in worksheets.

Suppose the user fill all 15 textbox .Then , first row of textbox will pass and store the data in Excel cells A1,B1,C1,D1 correspondingly .The second row of textbox store the data in Excel cells A2,B2,C2,D2 ......

Hence, I want to use nested loop to solve the problem .

 for {for { Cells.(i,j)=textbox1.value

The left side of the statement is fine ,as every Excel cells have their own ID (column and row order ) .

However ,for the right hand side ,every TextBox names are assigned by me .I name them as textbox1 ,textbox2 ,textbox3...

To let them fit in the loop, i want to change their textbox name during the loop .For example ,

`textbox+i; i++ ;` ' just my thought

Is it possible to do this in reality ? Besides,what if the users fill in data in half row (3 row with data then submit , 2 row blank) .How VBA continue to fill in the data following the row with data?

This question (http://stackoverflow.com/questions/2944693/how-to-fill-many-texbox-by-using-loop-function-in-vba) shows how to reference Textboxes as an array. (Especially easy if they are named "Textbox1", "Textbox2", etc) — YowE3K
– YowE3K, Commented Jul 18, 2016 at 3:34

user3598756 · Accepted Answer · 2016-07-18 08:52:40Z

1

you may use the following code:

For i = 1 To 5 '<-- 5 "rows" of textboxes
    For j = 1 To 3 '<-- 3 "columns" of textboxes for each row
        Cells(i, j) = Me.Controls("TextBox" & (i - 1) * 3 + j) '<-- textboxes are name form "TextBox1" to "TextBox15" rowwise 
    Next j
Next i

to be adjusted as per your actual rows and columns number.

as for your "what if..." issue, it's not clear to me what you want to achieve

should just want to skip "not entered" textboxes, then you could simply go

    For i = 1 To 5 '<-- 5 "rows" of textboxes
        For j = 1 To 3 '<-- 3 "columns" of textboxes for each row
            With Me.Controls("TextBox" & (i - 1) * 3 + j) '<-- textboxes are name form "TextBox1" to "TextBox15" rowwise
                If .Text <> "" Then Cells(i, j) = .Text '<--| skip void textboxes
            End With
        Next j
    Next i

answered Jul 18, 2016 at 8:52

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Monchhichi Over a year ago

another issue . As the worksheet going to paste contained old records or entries , how can the VBA paste the new entries to the corresponding new row .For example , the latest old entries in worksheet is on row 200 .How can the VBA know and paste the new entries fromthe user input box starting from row 201 to row 205 .(supposed that 5 new records in user interface )

user3598756 Over a year ago

If I fulfilled your original question please mark my answer as accepted. Thank you. You can retrieve the last row of the active worksheet in a variable like lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 and then use it to shift rows in Cells(lastRow + i, j)

Perry Sugerman · Accepted Answer · 2016-07-18 03:17:37Z

0

There is a method in the Application Object of Excel. This function will evaluate a string as a formula. URL to Excel Help Evaluate example:

https://msdn.microsoft.com/en-us/library/ff193019.aspx

answered Jul 18, 2016 at 3:17

Perry Sugerman

1781 silver badge12 bronze badges

Comments

Karthick Gunasekaran · Accepted Answer · 2016-07-18 04:55:17Z

0

Try with below code

Private Sub CommandButton1_Click()
    Dim ts As Control
    For Each ts In Me.Controls
        If ts.Name = "TextBox1" Then
            'do
        ElseIf ts.Name = "TextBox2" Then
            'do
        ElseIf ts.Name = "TextBox3" Then
            'do
        ElseIf ts.Name = "TextBox4" Then
            'do
        End If
    Next
End Sub

answered Jul 18, 2016 at 4:55

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to set textbox name as variable and allow to alter the name in loop?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related