Retreive row numbers back into userform

[PeterMC]

Hi,

I have to send up to 4 rows of data to a worksheet, from a userform. Each one should have an incrementing id number. I would like to return those id numbers, to the form, to be sent to another worksheet.

Can anyone advice please


If Not Me.TextBox16 = "" And Not Me.TextBox17 = "" And Not Me.TextBox25 = "" Then
MsgBox Send details to sheet2 for 1"' 'This will add a new row on sheet2 and make a new id number. If this is sent, get the id number.
Else
MsgBox "1 is blank"
End If
If Not Me.TextBox19 = "" And Not Me.TextBox20 = "" And Not Me.TextBox28 = "" Then
MsgBox " Send details to sheet2 for 2" 'This will add a new row on sheet2 and make a new id number. If this is sent, get the id number.
Else
MsgBox "2 is blank"
End If
If Not Me.TextBox22 = "" And Not Me.TextBox23 = "" And Not Me.TextBox30 = "" Then
MsgBox " Send details to sheet2 for 3" 'This will add a new row on sheet2 and make a new id number. If this is sent, get the id number.
Else
MsgBox "3 is blank"
End If
If Not Me.TextBox32 = "" And Not Me.TextBox33 = "" And Not Me.TextBox34 = "" Then
MsgBox " Send details to sheet2 for 4" 'This will add a new row on sheet2 and make a new id number. If this is sent, get the id number.
Else
MsgBox "4 is blank"
End If

[mancubus]

how do you 'send' the details to Sheet?
because you provided a MsgBox statement only.

the answer to this question is also an answer to second one.

[PeterMC]

On reading between the lines, of your comment Mancubus, I have put together a test sheet that gives what I was looking for. Maybe you could have a quick look and confirm that I am doing this the right way. Thank you

[mancubus]

ok... if i understand correctly....

in order to write a value to two different worksheets' cells you don't to store that value in a variable.

below code will write the id number to both Sheet1 and Sheet2. (and not the comments in textboex. if you need to write the comments too, add another line as in Sheet1)



Private Sub CommandButton1_Click()

Dim iRow As Long

With Worksheets("Sheet1")
If Len(Trim(TextBox1.Text)) > 0 Then
iRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = TextBox1.Text
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = iRow - 1
End If

If Len(Trim(TextBox2.Text)) > 0 Then
iRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = TextBox2.Text
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = iRow - 1
End If

If Len(Trim(TextBox3.Text)) > 0 Then
iRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = TextBox3.Text
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = iRow - 1
End If

If Len(Trim(TextBox4.Text)) > 0 Then
iRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = TextBox4.Text
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = iRow - 1
End If

If Len(Trim(TextBox5.Text)) > 0 Then
iRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = TextBox5.Text
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = iRow - 1
End If

If Len(Trim(TextBox6.Text)) > 0 Then
iRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = TextBox6.Text
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = iRow - 1
End If
End With

End Sub

[mancubus]

if the names of the textboxes are TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, from 1 to 6 in order, you can shorten the code like:



Private Sub CommandButton2_Click()

Dim iRow As Long

With Worksheets("Sheet1")
For i = 1 To 6
If Len(Trim(Me.Controls("TextBox" & i).Text)) > 0 Then
iRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(iRow, 1).Value = iRow - 1
.Cells(iRow, 2).Value = Me.Controls("TextBox" & i).Text
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = iRow - 1
End If
Next
End With

End Sub


be very carefull when using this.
you can change Me to insertlist.

cheers.