chrysw
09-16-2010, 11:04 AM
Private Sub CommandButton1_Click()
Const xlDown = &HFFFFEFE7
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
BlankRow = ws.Cells(1, 1).End(xlDown).Row + 1 'Finds first blank row in Column A
i = BlankRow
j = 1
Dim ctrl As MSForms.Control
For Each ctrl In UserForm1.Controls
If ctrl.Name Like "txt*" Then
ws.Cells(i, j).Value = ctrl.Value
End If
j = j + 1
Next ctrl
End Sub
I have created a form in Excel. What I would like to do is for each textbox (or a control with a value) to send the values to the worksheet. I'm trying to loop through each control instead of assigning a specific cell to each control. Below is the code that I've been working with.
Can anyone tell me what I am doing wrong w/ this code?
Thanks
Chrys
Const xlDown = &HFFFFEFE7
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
BlankRow = ws.Cells(1, 1).End(xlDown).Row + 1 'Finds first blank row in Column A
i = BlankRow
j = 1
Dim ctrl As MSForms.Control
For Each ctrl In UserForm1.Controls
If ctrl.Name Like "txt*" Then
ws.Cells(i, j).Value = ctrl.Value
End If
j = j + 1
Next ctrl
End Sub
I have created a form in Excel. What I would like to do is for each textbox (or a control with a value) to send the values to the worksheet. I'm trying to loop through each control instead of assigning a specific cell to each control. Below is the code that I've been working with.
Can anyone tell me what I am doing wrong w/ this code?
Thanks
Chrys