Transfer Data from UserForm to Worksheet
 

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---


--
RyGuy

Transfer Data from UserForm to Worksheet
 

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
--
jb


"ryguy7272" wrote:

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---


--
RyGuy

Transfer Data from UserForm to Worksheet
 

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" wrote:

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---


--
RyGuy

Transfer Data from UserForm to Worksheet
 

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---

--
RyGuy


"Joel" wrote:

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" wrote:

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---


--
RyGuy

Transfer Data from UserForm to Worksheet
 

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

--
jb


"ryguy7272" wrote:

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---

--
RyGuy


"Joel" wrote:

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" wrote:

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---


--
RyGuy

Transfer Data from UserForm to Worksheet
 

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
--
jb


"ryguy7272" wrote:

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---

--
RyGuy


"Joel" wrote:

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" wrote:

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---


--
RyGuy

Transfer Data from UserForm to Worksheet
 

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

Have a great day!
Ryan---

--
RyGuy


"john" wrote:

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
--
jb


"ryguy7272" wrote:

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---

--
RyGuy


"Joel" wrote:

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" wrote:

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---


--
RyGuy

Transfer Data from UserForm to Worksheet
 

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


"ryguy7272" wrote:

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

Have a great day!
Ryan---

--
RyGuy


"john" wrote:

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
--
jb


"ryguy7272" wrote:

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---

--
RyGuy


"Joel" wrote:

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" wrote:

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---


--
RyGuy

Transfer Data from UserForm to Worksheet
 

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

--
Rick (MVP - Excel)


"ryguy7272" wrote in message
...
The last one was right on! Thanks John. i learned something new today!!

Have a great day!
Ryan---

--
RyGuy


"john" wrote:

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
--
jb


"ryguy7272" wrote:

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---

--
RyGuy


"Joel" wrote:

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" wrote:

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---


--
RyGuy