CODEWITHSUNDEEP
vbaexceltextbox
Monchhichi
Monchhichi

Reputation: 397

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

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 .

textbox snapshot

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?

Upvotes: 0

Views: 18697

Answers (3)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 1

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

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

Upvotes: 0

Perry Sugerman
Perry Sugerman

Reputation: 178

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

Upvotes: 0

Related Questions