Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
Dim irow, icol As Long
    Dim ws As Worksheet
    Set ws = Worksheets("DataInput")
    'find first empty row in database
    irow = ws.Cells(Rows.Count, 2)
        .End(xlUp).Offset(1, 0).Row
    'copy the data to the database
    ws.Cells(irow, 2).Value = Me.Label23.Caption
    ws.Cells(irow, 3).Value = Me.TextBox1.Value
    ws.Cells(irow, 4).Value = Me.TextBox2.Value
    ws.Cells(irow, 5).Value = Me.TextBox66.Value
    ws.Cells(irow, 6).Value = Me.TextBox5.Value
    ws.Cells(irow, 7).Value = Me.TextBox57.Value
    ws.Cells(irow, 8).Value = Me.ComboBox1.Value
    ws.Cells(irow, 9).Value = Me.TextBox6.Value
    ws.Cells(irow, 10).Value = Me.TextBox7.Value
    ws.Cells(irow, 11).Value = Me.TextBox10.Value
    ws.Cells(irow, 12).Value = Me.TextBox12.Value
    ws.Cells(irow, 2).Value = Me.TextBox17.Value
    ws.Cells(irow, 3).Value = Me.TextBox20.Value
    ws.Cells(irow, 4).Value = Me.TextBox21.Value
    ws.Cells(irow, 16).Value = Me.TextBox22.Value
    ws.Cells(irow, 17).Value = Me.TextBox25.Value
    ws.Cells(irow, 18).Value = Me.TextBox26.Value
    ws.Cells(irow, 19).Value = Me.TextBox27.Value
    ws.Cells(irow, 20).Value = Me.TextBox30.Value
    ws.Cells(irow, 21).Value = Me.TextBox31.Value
    ws.Cells(irow, 22).Value = Me.TextBox37.Value
    ws.Cells(irow, 23).Value = Me.TextBox40.Value
    ws.Cells(irow, 24).Value = Me.TextBox41.Value
    ws.Cells(irow, 25).Value = Me.TextBox42.Value
    ws.Cells(irow, 26).Value = Me.TextBox45.Value
    ws.Cells(irow, 27).Value = Me.TextBox46.Value
    ws.Cells(irow, 28).Value = Me.TextBox47.Value
    ws.Cells(irow, 29).Value = Me.TextBox50.Value
    ws.Cells(irow, 30).Value = Me.TextBox51.Value
    ws.Cells(irow, 31).Value = Me.TextBox52.Value
    ws.Cells(irow, 32).Value = Me.TextBox55.Value
    ws.Cells(irow, 33).Value = Me.TextBox56.Value
    ws.Cells(irow, 34).Value = Me.TextBox67.Value
    ws.Cells(irow, 35).Value = Me.TextBox70.Value
    ws.Cells(irow, 36).Value = Me.TextBox71.Value
    ws.Cells(irow, 37).Value = Me.TextBox72.Value
    ws.Cells(irow, 38).Value = Me.TextBox75.Value
    ws.Cells(irow, 39).Value = Me.TextBox76.Value
    ws.Cells(irow, 40).Value = Me.TextBox77.Value
    ws.Cells(irow, 41).Value = Me.TextBox80.Value
    ws.Cells(irow, 42).Value = Me.TextBox81.Value
    ws.Cells(irow + 1, 1).Value = Me.TextBox1.Value
    ws.Cells(irow + 1, 2).Value = Me.TextBox2.Value
    ws.Cells(irow + 1, 3).Value = Me.TextBox4.Value
    ws.Cells(irow + 1, 4).Value = Me.ComboBox1.Value
    ws.Cells(irow + 1, 5).Value = Me.TextBox5.Value
    ws.Cells(irow + 1, 6).Value = Me.TextBox6.Value
    ws.Cells(irow + 1, 7).Value = Me.TextBox7.Value
    ws.Cells(irow + 1, 8).Value = Me.TextBox8.Value
    ws.Cells(irow + 1, 9).Value = Me.TextBox9.Value
    ws.Cells(irow + 1, 10).Value = Me.TextBox10.Value
    
    'Clear the data
    'Me.TextBox1.Value = ""
    'Me.TextBox2.Value = ""
    'Me.TextBox5.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox12.Value = ""
    Me.TextBox21.Value = ""
    Me.TextBox26.Value = ""
    Me.TextBox31.Value = ""
    Me.TextBox41.Value = ""
    Me.TextBox46.Value = ""
    Me.TextBox51.Value = ""
    Me.TextBox56.Value = ""
    Me.TextBox71.Value = ""
    Me.TextBox76.Value = ""
    Me.TextBox81.Value = ""
    'Me.TextBox1.SetFocus
    If CommandButton1 Then
        UserForm1.Hide
    End If

End Sub

Code: Select all

.....
For i = 1 to 2
     ws.cells(irow + i - 1, 10).value = me.controls( "TextBox" & 1 + 6*(i - 1)).value
     ws.cells(irow + i - 1, 11).value = me.controls( "TextBox" & 2 + 6*(i - 1)).value
     ws.cells(irow + i - 1, 12).value = me.controls( "TextBox" & 3 + 6*(i - 1)).value
     ws.cells(irow + i - 1, 13).value = me.controls( "TextBox" & 4 + 6*(i - 1)).value
     ws.cells(irow + i - 1, 14).value = me.controls( "TextBox" & 5 + 6*(i - 1)).value
     ws.cells(irow + i - 1, 15).value = me.controls( "TextBox" & 6 + 6*(i - 1)).value
Next i
.....

Code: Select all

Private Sub CommandButton1_Click()
On Error Resume Next
Dim irow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("DataInput")
    'find first empty row in database
    irow = ws.Cells(Rows.Count, 2)
        '.End(xlUp).Offset(1, 0).Row
    'copy the data to the database
For i = 1 To 11
     ws.Cells(irow + i - 1, 2).Value = Me.Controls("TextBox1" & 1 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 3).Value = Me.Controls("TextBox2" & 2 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 4).Value = Me.Controls("TextBox3" & 3 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 5).Value = Me.Controls("TextBox4" & 4 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 6).Value = Me.Controls("ComboBox1" & 5 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 7).Value = Me.Controls("TextBox5" & 6 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 8).Value = Me.Controls("TextBox6" & 7 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 9).Value = Me.Controls("TextBox7" & 8 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 10).Value = Me.Controls("TextBox8" & 9 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 11).Value = Me.Controls("TextBox9" & 10 + 11 * (i - 1)).Value
     ws.Cells(irow + i - 1, 12).Value = Me.Controls("TextBox10" & 11 + 11 * (i - 1)).Value
Next i
    'Clear the data
    'Me.TextBox1.Value = ""
    'Me.TextBox2.Value = ""
    'Me.TextBox5.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox12.Value = ""
    Me.TextBox18.Value = ""
    Me.TextBox24.Value = ""
    Me.TextBox30.Value = ""
    Me.TextBox36.Value = ""
    Me.TextBox42.Value = ""
    Me.TextBox48.Value = ""
    Me.TextBox54.Value = ""
    Me.TextBox60.Value = ""
    Me.TextBox66.Value = ""
    Me.TextBox72.Value = ""
    'Me.TextBox1.SetFocus
    If CommandButton1 Then
        UserForm1.Hide
    End If

End Sub