Userform Textboxes and Looping

[Guest]

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]

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

 

[Guest]

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

Robbyn