Excel Forum

Code: Select all

ws.Cells(irow, 10).Value = Me.TextBox9.Value
ws.Cells(irow, 11).Value = Me.TextBox10.Value

Code: Select all

Private Sub Save_Click()
    Dim irow As Long
    Dim ws As Worksheet
    Dim objP As Variant
    Dim objF As Variant
    Dim objD As Variant
    Dim i As Integer
    Set ws = Worksheets("tracking")
    objP = Array(Me.TextBox8, Me.TextBox11, Me.TextBox14, _
        Me.TextBox17, Me.TextBox23, Me.TextBox26)
    objF = Array(Me.TextBox9, Me.TextBox12, Me.TextBox15, _
        Me.TextBox18, Me.TextBox24, Me.TextBox27)
    objD = Array(Me.TextBox10, Me.TextBox13, Me.TextBox16, _
        Me.TextBox19, Me.TextBox25, Me.TextBox28)
        
    If Me.TextBox1.Value <> "" Then
        Me.TextBox1 = Application.Trim(Me.TextBox1.Text)
        Me.TextBox7 = Application.Trim(Me.TextBox7.Text)
        'Find first empty row in database
        irow = ws.Cells(Rows.Count, 2) _
            .End(xlUp).Offset(1, 0).Row
            
        'Copy the Data To The Databse
        For i = o To UBound(objP)
             If objP(i).Text <> "" Then
                ws.Cells(irow, 2).Value = Me.TextBox1.Value
                ws.Cells(irow, 3).Value = Me.TextBox2.Value
                ws.Cells(irow, 4).Value = Me.TextBox3.Value
                ws.Cells(irow, 5).Value = Me.TextBox4.Value
                ws.Cells(irow, 6).Value = Me.TextBox5.Value
                ws.Cells(irow, 7).Value = Me.TextBox6.Value
                ws.Cells(irow, 8).Value = Me.TextBox7.Value
                ws.Cells(irow, 9).Value = objP(i).Value
                ws.Cells(irow, 10).Value = objF(i).Value
                ws.Cells(irow, 11).Value = objD(i).Value
                irow = irow + 1
            End If
        Next i
    Else
        MsgBox "Please check Data", vbCritical
    End If
End Sub

Code: Select all

Private Sub UserForm_Initialize()
TextBox2.Value = ("iJOBs-WP-001")
End Sub

Code: Select all

Private Sub UserForm_Initialize()
TextBox2.Value = Format(Application.Max(Sheets("Tracking").Range("c:c")) + 1, "000")
End Sub

Code: Select all

Private Sub UserForm_Initialize()
    Dim t As String
    TextBox1.Value = Date
    TextBox7.Value = Date
    t = Sheets("Tracking").Range("c" & Rows.Count).End(xlUp).Value
    TextBox2.Value = "iJOBs-WP-"
    TextBox2.Value = TextBox2.Value & Format(Replace(t, TextBox2.Value, "") + 1, "000")
    a = "code!B6:b20"
End Su

Code: Select all

Private Sub Save_Click()
    Dim irow As String
    Dim ws As Worksheet
    Dim objP As Variant
    Dim objF As Variant
    Dim objD As Variant
    Dim i As Integer
    Set ws = Worksheets("tracking")
    objP = Array(Me.TextBox8, Me.TextBox11, Me.TextBox14, _
        Me.TextBox17, Me.TextBox20, Me.TextBox23, Me.TextBox26, _
        Me.TextBox29, Me.TextBox32, Me.TextBox35, Me.TextBox38, _
        Me.TextBox41, Me.TextBox44, Me.TextBox47, Me.TextBox50, _
        Me.TextBox53, Me.TextBox56, Me.TextBox59)
    objF = Array(Me.TextBox9, Me.TextBox12, Me.TextBox15, _
        Me.TextBox18, Me.TextBox21, Me.TextBox24, Me.TextBox27, _
        Me.TextBox30, Me.TextBox33, Me.TextBox36, Me.TextBox39, _
        Me.TextBox42, Me.TextBox45, Me.TextBox48, Me.TextBox51, _
        Me.TextBox54, Me.TextBox57, Me.TextBox60)
    objD = Array(Me.TextBox10, Me.TextBox13, Me.TextBox16, _
        Me.TextBox19, Me.TextBox22, Me.TextBox25, Me.TextBox28, _
        Me.TextBox31, Me.TextBox34, Me.TextBox37, Me.TextBox40, _
        Me.TextBox43, Me.TextBox46, Me.TextBox49, Me.TextBox52, _
        Me.TextBox55, Me.TextBox58, Me.TextBox61)
        
    If Me.TextBox1.Value <> "" Then
        Me.TextBox1 = Application.Trim(Me.TextBox1.Text)
        Me.TextBox7 = Application.Trim(Me.TextBox7.Text)
        'Find first empty row in database
        irow = ws.Cells(Rows.Count, 2) _
            .End(xlUp).Offset(1, 0).Row
            
        'Copy the Data To The Databse
            For i = o To UBound(objP)
             If objP(i).Text <> "" Then
                ws.Cells(irow, 2).Value = Me.TextBox1.Value
                ws.Cells(irow, 3).Value = Me.TextBox2.Value
                ws.Cells(irow, 4).Value = Me.TextBox3.Value
                ws.Cells(irow, 5).Value = Me.TextBox4.Value
                ws.Cells(irow, 6).Value = Me.TextBox5.Value
                ws.Cells(irow, 7).Value = Me.TextBox6.Value
                ws.Cells(irow, 8).Value = Me.TextBox7.Value
                ws.Cells(irow, 9).Value = Me.TextBox62.Value
                ws.Cells(irow, 10).Value = objP(i).Value
                ws.Cells(irow, 11).Value = objF(i).Value
                ws.Cells(irow, 12).Value = objD(i).Value
                irow = irow + 1
            End If
        Next i
    Else
        MsgBox "Please check Data", vbCritical
    End If
End Sub

Private Sub Com1_Click()
Unload UserForm1
End Sub
Private Sub UserForm_Initialize()
    TextBox1.Value = Date
    TextBox7.Value = Date
    Dim t As String
    TextBox1.Value = Date
    TextBox7.Value = Date
    t = Sheets("Tracking").Range("c" & Rows.Count).End(xlUp).Value
    TextBox2.Value = "iJOBs-WP-"
    TextBox2.Value = TextBox2.Value & Format(Replace(t, TextBox2.Value, "") + 1, "000")
End Sub