I'm confused at where your textboxes are. Are they on an actual UserForm
(designed in the VBE) or are they placed on a worksheet that looks like a form
the users would use?

And if they are on a worksheet, are the textboxes from the Drawing toolbar or
from the Control toolbox toolbar?

Your question says from textboxes to userform, but your code looks more like
it's from textboxes on a userform to cells in a worksheet...

If that's what you're doing, then this worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()

Dim Rng As Range
Dim i As Long
Dim ctrl As Control

With Worksheets("Sheet1")
Range("A1").Select
If ActiveCell.Offset(1, 0) = "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.End(xlDown).Offset(1, 0).Select
End If
Set Rng = ActiveCell
With Rng
i = 1
For Each ctrl In Me.Controls
If TypeOf ctrl Is msForms.TextBox Then
.Offset(0, i).Value = ctrl.Value
i = i + 1
End If
Next ctrl
End With
End With
End Sub

and most of the time, you don't have to select cells to work with them.

I determined the next available row by starting at the bottom of column A and
coming up. If that works in your situation (it doesn't always!), then I like
this better:

Option Explicit

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim i As Long
Dim ctrl As Control

With Worksheets("Sheet1")
Set Rng = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
With Rng
i = 1
For Each ctrl In Me.Controls
If TypeOf ctrl Is msForms.TextBox Then
.Offset(0, i).Value = ctrl.Value
i = i + 1
End If
Next ctrl
End With
End With
End Sub



Robbyn wrote:
>
> Good evening,
>
> I'm trying to move data from textboxes into Userform, but the following code
> doesn't work (I've tried many variations to no avail). What am I doing wrong?
>
> With Worksheets("Sheet1")
> Range("A1").Select
> If ActiveCell.Offset(1, 0) = "" Then
> ActiveCell.Offset(1, 0).Select
> Else
> ActiveCell.End(xlDown).Offset(1, 0).Select
> End If
> Set rng = ActiveCell
> With rng
> i = 1
> For Each ctrl In Me.Controls
> If TypeOf ctrl Is msForms.TextBox Then<----Runtime error 451
> .Offset(0, i).Value = ctrl(i).Value
> i = i + 1
> End If
> Next ctrl
> End With
> End With

--

Dave Peterson