Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping through textboxes | Excel Discussion (Misc queries) | |||
UserForm TextBoxes | Excel Discussion (Misc queries) | |||
userform & textboxes | Excel Programming | |||
userform textboxes again | Excel Programming | |||
userform textboxes | Excel Programming |