Load multiple Textbox.text entries to a sheet

[jrobin5881]

Good Morning,

I have a userform with multiple textboxes and a "Submit" command button below. I want the user to add values, text to the textboxes and have the data post to a master sheet when they're done. I know how to do this with one textbox at a time however I'm trying to streamline the code to loop through all the textboxes and complete the task in one shot.

My textboxes are named "txt2045, txt2046.... txtbox20413". The .text will begin at row 4 in sheet29 in column 4 and end at row 13.

I set up a For Next loop using "t" as the variable for the remainder of textbox name and "i" as the variable for the row number.

The code does not like me using "t" as the remainder of the textbox name. What am i doing wrong?

SAMPLE

Private Sub CommandButton6_Click()
    
    Dim t As Integer
    Dim i As Integer
    
    For t = 5 To 14
        i = 4
        Sheet29.Select
        Cells(i, 4) = txt204 & t & .Text
        
        i = i + 1
    Next
    
End Sub

 

[PHV]

Sheet29.Cells(i, 4) = Me.Controls("txt204" & t).Text

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jrobin5881]

PHV

Thanks but its not working? It's not crashing and I'm not getting an error message but the text from the textbox is not moving to the recap sheet (sheet29).

See ammended code:

Private Sub CommandButton6_Click()
    
    Dim t As Integer
    Dim i As Integer
    
    For t = 5 To 14
        i = 4
        Sheet29.Cells(i, 4) = Me.Controls("txt204" & t).Text
        
        i = i + 1
    Next
    
    Unload Me
    frmMain.Show
    
End Sub

 

[SkipVought]

Is it Sheet29 or "Sheet29".

The former is a CodeName. The latter is a Sheet Name.

Not necessarily the same thing...

Sheet29.Cells(i, 4) = Me.Controls("txt204" & t).Text
Sheets("Sheet29").Cells(i, 4) = Me.Controls("txt204" & t).Text

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin5881]

Hey Skip!,

I'm trying to get better at my coding so instead of using the sheet name ("Quarterly") in this instance, I was using the the sheet number. I picked that up from some reading on did on the net that said it's better to use the number of the sheet rather then the name so that if the sheet name changes your code would always be solid.

When I used

 Sheets("Sheet29").Cells(i, 4) = Me.Controls("txt204" & t).Text

I recieved a subscript out of range message. I then changed it to

 Sheets("Quarterly").Cells(i, 4) = Me.Controls("txt204" & t).Text

and once again I get no error message and hte code seems to execute but the value does not show up on the "Quarterly" worksheet.

PS - How can I step through this code to check it? It's on a command click event of a button and the normal F8 process does not work.

 

[SkipVought]

There should be no question about what the sheet CodeName and Sheet Name is. Is it right in front of you in the Project Explorer.

once again I get no error message and hte code seems to execute but the value does not show up on the "Quarterly" worksheet.

STEP thru the code or use Debug.Print in your code to write values into the Immediate Window.

What is the values of i, t & Me.Controls("txt204" & t).Text???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin5881]

Yes I have my Project Explorer window open and it says Sheet29(Quarterly)

I'll have to do more reading on the use of the immediate window for debuggin.

txt2045 is the first textbox on the form. The second one is txt2046 is the second one and so on. So coming into the loop the value of the variable t is set to 5. The end user enters a number or text into the textbox.

I want to transfer the text or number in txt2045 or as represented in code(txt204 & t) to Sheet29 named ("Quarterly") into cell D4 or as represented in code(cells(i,4)).

I want to loop back up, change the i variable to 5 and the t variable to 6. The code should then transfer the text in txt2046 or as represented in code(txt204 & t) to Sheet29 named ("Quarterly") cell D5 or as represented in code(Cells(i,4))

I want to continue looping through the rest of the textboxes and posting to Sheet29(Quarterly)

 

[SkipVought]

Private Sub CommandButton6_Click()
    
    Dim t As Integer
    Dim i As Integer
   [b] 
    i = 4[/b]
    For t = 5 To 14
        Sheet29.Cells(i, 4) = Me.Controls("txt204" & t).Text
        [b]debug.print i & ":" & t & ":" & Me.Controls("txt204" & t).Text[/b]
        i = i + 1
    Next
    
    Unload Me
    frmMain.Show
    
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin5881]

Skip,

Thanks BUNCHES!!! I learned a heap through this exercise and my coding is now much tighter. Through your guidance I see where I was going wrong. I have to set the value of the variable "i" before I dropped into the loop.

I did some reading on the debug.print and definately see the value in it. My thanks again to you and PHV.

Now I'm going to use the loop again to reset the text to nulls on the userform before I close it.

JR

 

[fumei]

I have to set the value of the variable "i" before I dropped into the loop. "

Yes, because it IS a loop.

    For t = 5 To 14
        [b]i = 4[/b]
        Sheet29.Cells(i, 4) = Me.Controls("txt204" & t).Text
        
        i = i + 1
    Next

sets i = 4 for every iteration of the loop. Yes, you use i = i + 1, but every time the loop actions again i = 4 again.

"Now I'm going to use the loop again to reset the text to nulls on the userform before I close it. "

Do you mean the text in the userform textboxes? Why? When you close the userform the textboxes are gone. Is there a point to making them empty before closing the userform?

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[jrobin5881]

Fumei,

Thanks for the followup. Here's what was ocurring (which I didn't want):

The Cells(4,4) is where the text from the first txtbox needed to go. So when i = 4 was inside the loop it kept changing i back to 4 with each iteration.

For t = 5 To 14
        i = 4
        Sheet29.Cells(i, 4) = Me.Controls("txt204" & t).Text
        i = i + 1
Next

The reason I was not getting an error message and could not see anything in Cells(4,4) was due to me only placing a value in the first textbox and then checking my code. So, first iteration sees my value, drops it in Cells(4,4) and adds 1 to i making it = to 5. It drops down and loops back up and t = 6 and then it hits the i=4 line and changes it from 5 to back to 4. My second text box had no value in it
so it places " " in Cells(4,4) and we continue the loop until t = 14. So unless I had a value in the last textboxt the loop was doing what it was supposed.

Skip found the issue out rather quickly for me and we set the value of i to 4 BEFORE going into the loop and incrementally the code moved the responses down the sheet row by row in the same column.

I thought about your advice about the code to clear the txtboxes prior to unload and your right so I took it out.

I really appreciate your feedback on this stuff.

JR