copy from userform to database sheet if textbox has a value

[Ricardasr25]

Good evening everybody I'm new to VBA,

I cant figure it out how to write the code.

Basically i have 25 labels and 5 textboxes next to these labels in userform it looks like that: lbl1 lbl2 lbl3 lbl4 lbl5 Textbox1
lbl6 lbl7 lbl8 lbl9 lbl10 Textbox2
lbl11 lbl12 lbl13 lbl14 lbl15 Textbox3
lbl16 lbl17 lbl18 lbl19 lbl20 Textbox4
lbl21 lbl22 lbl23 lbl24 lbl25 Textbox5
What i would like to achieve is if textbox has a value copy whole row from userform to database sheet, for example if:
lbl1 lbl2 lbl3 lbl4 lbl5 Textbox1 = 21(21 will be typed in to textbox)
lbl6 lbl7 lbl8 lbl9 lbl10 Textbox2
lbl11 lbl12 lbl13 lbl14 lbl15 Textbox3 = 12(12 will be typed in to textbox)
lbl16 lbl17 lbl18 lbl19 lbl20 Textbox4
lbl21 lbl22 lbl23 lbl24 lbl25 Textbox5 = 14(14 will be typed in to textbox)
So as you can see from this example when i press a button i would like to only copy caption of these three rows to database sheet : (because only three textboxes were filled other two rows should be skipped)
lbl1 lbl2 lbl3 lbl4 lbl5 Textbox1
lbl11 lbl12 lbl13 lbl14 lbl15 Textbox3
lbl21 lbl22 lbl23 lbl24 lbl25 Textbox5
If somebody can help on this code i would be very thankfull.

 

[Worf]

Good evening

please test this:

Private Sub CommandButton1_Click()
Dim i%, ir%, j%, s%


For i = 1 To 5      ' the textboxes
    If Len(Me.Controls("TextBox" & i).Value) > 0 Then
        ir = Sheets("Sheet2").Range("a" & Rows.Count).End(xlUp).Row + 1
        s = 5 * i - 4
        For j = 1 To 5      ' columns
            Sheets("Sheet2").Cells(ir, j).Value = Me.Controls("Label" & s).Caption
            s = s + 1       ' next label
        Next
    End If
Next
End Sub

 

[Ricardasr25]

Something is wrong in

Hi the code stops at 6th line where it sais "Sheets(Sheet2)"
error comes at "could not specify object"
Thanks in advance

[COLOR=#333333]Private Sub CommandButton1_Click()[/COLOR]
Dim i%, ir%, j%, s%For i = 1 To 5      ' the textboxes    If Len(Me.Controls("TextBox" & i).Value) > 0 Then        ir = Sheets("Sheet2").Range("a" & Rows.Count).End(xlUp).Row + 1        s = 5 * i - 4        For j = 1 To 5      ' columns            Sheets("Sheet2").Cells(ir, j).Value = Me.Controls("Label" & s).Caption            s = s + 1       ' next label        Next    End IfNext </pre>[COLOR=#333333]End Sub[/COLOR]

 

[Ricardasr25]

Hi Worf,
Sorry it works as it should do, but it's not copying the textbox value, maybe its possible to add that?

Thanks for your time

 

[Worf]

Oops, I forgot that:

Private Sub CommandButton1_Click()
Dim i%, ir%, j%, s%, sh As Worksheet, tbv


Set sh = Sheets("Sheet2")
For i = 1 To 5      ' the textboxes
    tbv = Me.Controls("TextBox" & i).Value
    If Len(tbv) > 0 Then
        ir = sh.Range("a" & Rows.Count).End(xlUp).Row + 1
        s = 5 * i - 4
        For j = 1 To 5      ' columns
            sh.Cells(ir, j).Value = Me.Controls("Label" & s).Caption
            s = s + 1       ' next label
        Next
        sh.Cells(ir, 6).Value = tbv
    End If
Next
End Sub