Loop textbox to cell

[chrysw]

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

[austenr]

Instead of this

BlankRow = ws.Cells(1, 1).End(xlDown).Row + 1 'Finds first blank row in Column A
i = BlankRow

why not use this


Dim LastRow As Long
LastRow = [A65536].End(xlUp).Row

Accomplishes the same thing

[austenr]

BTW, does this compile?

[chrysw]

Thanks for the suggestion austenr.

Technically your code or mine would work but it's not. The problems that I am having (with both Blankrow and LastRow) are:

1- it writes to cell B1, C1, D1, E1 for my 4 textboxes but not to A1 (which is an empty cell)

2- continues to overwrite instead of dropping to the next row or next empty cell

I've changed the code j = 1 to j = 0 which takes care of problem 1 but I'm still having the issue with problem 2.

Any other suggestions?

Thanks:banghead:

[austenr]

you need a way to move to the next line. Untested



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
i = i + 1 ' moves to the next row down
End Sub

[chrysw]

I'm sorry and maybe I'm wrong but wouldn't (i = i + 1)
defeat the purpose of the Blankrow/LastRow where that is looking for the last row?

BTW . . . there is a slight correction to the code previously posted
BlankRow should not include .Row + 1

BlankRow = ws.Cells(1, 1).End(xlUp).Row 'Finds first blank row in Column A
i = BlankRow



Thanks again for your suggestion.