User Form Textbox looping

[sjtjr73]

Guys
I am having trouble looping through my textboxes on a user form. What is happening it either only add the last 4 text boxes to the spread sheet or add the first 4 skips 8 and adds last four also for some reason it deletes the the text from where the user form gets the data from.Code is below.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim sheet As String
Dim i As Integer
'Select the worksheet
Set ws = Worksheets("Crown")

'starts loop
For i = 1 To 30
  Controls("TextBox" & i).Value = ""

 
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 3).Value = Date
ws.Cells(iRow, 4).Value = Me.Textbox1.Text
ws.Cells(iRow, 5).Value = Me.TextBox2.Text
ws.Cells(iRow, 6).Value = Me.TextBox3.Text
ws.Cells(iRow, 7).Value = Me.TextBox4.Text
ws.Cells(iRow, 4).Value = Me.TextBox5.Text
ws.Cells(iRow, 5).Value = Me.TextBox6.Text
ws.Cells(iRow, 6).Value = Me.TextBox7.Text
ws.Cells(iRow, 7).Value = Me.TextBox8.Text
ws.Cells(iRow, 4).Value = Me.TextBox9.Text
ws.Cells(iRow, 5).Value = Me.TextBox10.Text
ws.Cells(iRow, 6).Value = Me.TextBox11.Text
ws.Cells(iRow, 7).Value = Me.TextBox12.Text
ws.Cells(iRow, 4).Value = Me.TextBox13.Text
ws.Cells(iRow, 5).Value = Me.TextBox14.Text
ws.Cells(iRow, 6).Value = Me.TextBox15.Text
ws.Cells(iRow, 7).Value = Me.TextBox16.Text
ws.Cells(iRow, 4).Value = Me.TextBox17.Text
ws.Cells(iRow, 5).Value = Me.TextBox18.Text
ws.Cells(iRow, 6).Value = Me.TextBox19.Text
ws.Cells(iRow, 7).Value = Me.TextBox20.Text
ws.Cells(iRow, 4).Value = Me.TextBox21.Text
ws.Cells(iRow, 5).Value = Me.TextBox22.Text
ws.Cells(iRow, 6).Value = Me.TextBox23.Text
ws.Cells(iRow, 7).Value = Me.TextBox24.Text

'Ends loop
  Next i
  
  


End Sub

Thanks for any help I can get

 

[DanteAmor]

First you must put the textbox value in the cell and then clear the textbox.
Also, I assume that each textbox goes in a different column.

Try this:

Private Sub CommandButton1_Click()
  Dim i As Long, j As Long, iRow As Long
  Dim ws As Worksheet
 
  'Select the worksheet
  Set ws = Worksheets("Crown")
  'find first empty row in database
  iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
  j = 3
  For i = 1 To 30
    ws.Cells(iRow, j).Value = Controls("TextBox" & i).Text
    j = j + 1
    Controls("TextBox" & i).Value = ""
  Next
End Sub

----------------------------------------------------------------------

Or you need 4 textbox per row:

Private Sub CommandButton1_Click()
  Dim i As Long, j As Long, iRow As Long
  Dim ws As Worksheet
  
  'Select the worksheet
  Set ws = Worksheets("Crown")
  'find first empty row in database
  iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
  
  j = 4
  For i = 1 To 24
    ws.Cells(iRow, 3).Value = Date
    ws.Cells(iRow, j).Value = Controls("TextBox" & i).Text
    j = j + 1
    If j = 8 Then
      j = 4
      iRow = iRow + 1
    End If
    Controls("TextBox" & i).Value = ""
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[sjtjr73]

I am know getting runtime error -2147024809(80070057);
Could not find the specified object.
It still deletes all data and formulas on sheet 1 where the textbox pulls the data from. Maybe because its a linked textbox idk then when I click debug it highlights the below code. instead of link text boxes should I have it pull the data I need off the first page to keep from deleting everything?

Controls("TextBox" & i).Value = ""

 

[sjtjr73]

Yes on user form at the moment I have 6 Row of textboxes in 4 columns. I forgot to tell you that sorry. I am still getting same error as before

 

[sjtjr73]

User Form in pic. I will be adding at least 40 more textboxes. just want to get everything working first. Granted I might not use all 40 at 1 time.

 

[DanteAmor]

I have 6 Row of textboxes in 4 columns

That's 24 textbox, then try:

Private Sub CommandButton1_Click()
  Dim i As Long, j As Long, iRow As Long
  Dim ws As Worksheet
  
  'Select the worksheet
  Set ws = Worksheets("Crown")
  'find first empty row in database
  iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
  
  j = 4
  For i = 1 To 24
    ws.Cells(iRow, 3).Value = Date
    ws.Cells(iRow, j).Value = Controls("TextBox" & i).Text
    j = j + 1
    If j = 8 Then
      j = 4
      iRow = iRow + 1
    End If
    Controls("TextBox" & i).Value = ""
  Next
End Sub

 

[DanteAmor]

I will be adding at least 40 more textboxes.

For that I recommend modifying your design.
Only with 4 textboxes and a listbox.

Something like this:

That way you will only have to validate 4 textboxes and not 40.

And you can also have n number of records.

Try the following code with the new layout:

Private Sub CommandButton1_Click()

' Save to sheet

  Dim i As Long, j As Long, iRow As Long
  Dim ws As Worksheet
  
  Set ws = Worksheets("Crown")
  iRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
  
  With ListBox1
    For i = 0 To .ListCount - 1
      ws.Range("C" & iRow).Value = Date
      ws.Range("D" & iRow).Value = .List(i, 0)
      ws.Range("E" & iRow).Value = .List(i, 1)
      ws.Range("F" & iRow).Value = .List(i, 2)
      ws.Range("G" & iRow).Value = .List(i, 3)
      iRow = iRow + 1
    Next
    .Clear
  End With
End Sub

Private Sub CommandButton2_Click()

'Pass to Listbox

  Dim i As Long
  
  With ListBox1
    .AddItem
    For i = 1 To 4
      .List(.ListCount - 1, i - 1) = Controls("TextBox" & i).Value
      Controls("TextBox" & i).Value = ""
    Next
  End With
End Sub

Private Sub UserForm_Activate()
  ListBox1.ColumnCount = 4
End Sub

Result:

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[sjtjr73]

That looks like it will work. The data needs to be pulled from spread sheet input and be placed on crown. Because sheet Input has to be printed and sent to the receiver and Crown sheet is so I can keep up with the budget for that company. Pic below of the input spread sheet and second pic is Crown spread sheet. Is their away to skip the text boxes and just add everything to the list box then to the other sheet? I was trying to use Access but the problem is I can't create a report in access using the input sheet as a template.

 

[DanteAmor]

It seems to me that the requirement is different from the original post. Unfortunately I don't have access, at that point I can't help you.

If you are going to pass data from one sheet to another sheet I suggest creating a new thread since that is different from your original requirement.

 

[sjtjr73]

No I am going to stay with excel since it has better function and reports than access. I just need to get it to loop and add all data in the text boxes to the sheet crown. But for some reason it just adding 4 columns like it should but on the other 5 or 6 rows it adding the same 4 text boxes.