Userform Textboxes and Looping

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

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

Yup. I mistyped. Your assumption was right on the money and your code
worked. Thank you very much for your help.

Robbyn

"Dave Peterson" wrote:

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