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

Userform Textboxes and Looping
 

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

Userform Textboxes and Looping
 

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