Transfer Data from UserForm to Worksheet

[ryguy7272]

I have a total of 60 TextBoxes I am wondering if there is an easier way,
using a loop, to transfer the information from the UserFrom to the Worksheet
(in a row).

Private Sub cmdEnter_Click()
On Error Resume Next

Range(Worksheets("Import").Cells(Rows.Count, "A").End(xlUp).Address).Select
ActiveCell.Offset(1, 0).Select

If ActiveCell = "" Then

ActiveCell.Value = txtLastName.Value
ActiveCell.Offset(0, 1) = txtFirstName.Value
ActiveCell.Offset(0, 2) = txtMR.Value
ActiveCell.Offset(0, 3) = txtDate.Value
'skip Column 4, but I could end up using it, if it makes it easier to loop
ActiveCell.Offset(0, 5) = TextBox0.Value
ActiveCell.Offset(0, 6) = TextBox1.Value
ActiveCell.Offset(0, 7) = TextBox2.Value
ActiveCell.Offset(0, 8) = TextBox3.Value
ActiveCell.Offset(0, 9) = TextBox4.Value
ActiveCell.Offset(0, 10) = TextBox5.Value
‘etc., etc., etc.,
End If
End Sub

Thanks,
Ryan---

 

[john]

assuming all your textboxes were named Textbox1, TextBox2 etc etc you could
try following as an approach:

Private Sub cmdEnter_Click()
Dim i As Integer

With Worksheets("Import")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

For i = 1 To 60

If i <> 4 Then .Cells(lastrow, i).Value = Controls("TextBox" &
i).Value

Next i

End With

End Sub

 

[Joel]

Here are two versions of the code

Private Sub cmdEnter_Click()
'Version 1

With Worksheets("Import")
LastRow = .Range("A" & Rows.Count).End(xlUp)
NewRow = LastRow + 1
ColCount = 1
For i = 0 To 59

Do While .Cells(NewRow, ColCount) <> "" And _
ColCount = 4

ColCount = ColCount + 1
Loop

.Cells(NewRow, ColCount) = _
UserForm1.Controls("textbox" & i).Value

Next i
End With


'Version 2
BoxNames = Array("txtLastName", "txtFirstName", _
"txtMR", "txtDate")

With Worksheets("Import")
LastRow = .Range("A" & Rows.Count).End(xlUp)
NewRow = LastRow + 1
ColCount = 1
For i = LBound(BoxNames) To UBound(BoxNames)
Do While .Cells(NewRow, ColCount) <> "" And _
ColCount = 4

ColCount = ColCount + 1
Loop

.Cells(NewRow, ColCount) = _
UserForm1.Controls(BoxNames(i)).Value

Next i
End With
End Sub

 

[ryguy7272]

Thanks Joel! I tried both versions; both fail on this line:
NewRow = LastRow + 1

John, your code sort of worked, after I fiddled with it, but couldn't get it
quite right. Column A = LastName, Column B = FirstName, Column C = IDNumber,
Column D = Date, column E = "", Column F through Column BR = TextBox0 through
TextBox64

I tried the below, but the logic seems to be off a bit because it is not
populating anything, not even Column A - Column D:

ActiveCell.Value = txtLastName.Value
ActiveCell.Offset(0, 1) = txtFirstName.Value
ActiveCell.Offset(0, 2) = txtMR.Value
ActiveCell.Offset(0, 3) = txtDate.Value

Dim i As Integer

With Worksheets("Import")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 0 To 64
i = 6
.Cells(lastrow, i).Value = Controls("TextBox" & i).Value
Next i
End With

Any thoughts?

Thanks,
Ryan---

 

[john]

not tested but try something like this - adapt as required:

Private Sub cmdEnter_Click()
Dim i As Integer

With Worksheets("Import")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

.Cells(lastrow, 1).Value = txtLastName.Value
.Cells(lastrow, 2).Value = txtFirstName.Value
.Cells(lastrow, 3).Value = txtMR.Value
.Cells(lastrow, 4).Value = txtDate.Value


For i = 6 To 60

.Cells(lastrow, i).Value = Me.Controls("TextBox" & i).Text

Next i

End With

End Sub

 

[john]

sorry - did not fully read post - hope this works!

Private Sub cmdEnter_Click()
Dim i As Integer

With Worksheets("Import")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

.Cells(lastrow, 1).Value = txtLastName.Text
.Cells(lastrow, 2).Value = txtFirstName.Text
.Cells(lastrow, 3).Value = txtMR.Text
.Cells(lastrow, 4).Value = txtDate.Text


For i = 6 To 60

.Cells(lastrow, i).Value = Me.Controls("TextBox" & i - 6).Text

Next i

End With

End Sub

 

[ryguy7272]

The last one was right on! Thanks John. i learned something new today!!

Have a great day!
Ryan---

 

[john]

me too - read post fully before responding!
Thanks for feedback

 

[Rick Rothstein]

Here is another approach that produces simpler code and doesn't require
control names with consecutive numbering in them. Set the TabIndex of first
TextBox to 0 (this is easiest to start with, but you can start with any
TabIndex if you want, you will just have to subtract that starting number
from the referenced C.TabIndex in the code below), then sequentially number
the TabIndex'es of the rest of the TextBox in the order you want them
referenced. (Right click a blank section of the UserForm and pick Tab Order
from the pop up menu to help you place controls in the Tab Order sequence
you want.) After you have done that, you can use this code to move the
contents of your TextBox'es to the cells...

Private Sub CommandButton1_Click()
Dim C As Control
For Each C In Me.Controls
If TypeOf C Is MSForms.TextBox Then
ActiveCell.Offset(C.TabIndex).Value = C.Text
End If
Next
End Sub