Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Transfer Data from UserForm to Worksheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
transfer data from worksheet to another dave Excel Discussion (Misc queries) 1 September 4th 08 06:14 AM
Excel Worksheet transfer of Data MaggieB New Users to Excel 2 July 23rd 07 04:38 PM
transfer data from word userform to excel Jade Excel Programming 1 January 29th 07 09:15 PM
Transfer data from worksheet to array Pradip Jain Excel Programming 6 October 24th 06 09:03 PM
Transfer data from one worksheet to another CheriT63 Excel Programming 1 April 5th 06 03:17 AM


All times are GMT +1. The time now is 05:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement