Excel Forum

Code: Select all

Private Sub cmdExit_Click()
    Unload Me
End Sub
Private Sub cmdClear_Click()
'Clear the Form
    For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    End If
Next ctl
End Sub
Private Sub cmdSave_Click()
    Dim RowCount As Long
    Dim temprow As Long
    Dim ctl As Control
    'init row
    temprow = 1
' Check user input
    If Me.txtSparepartchange.Value = "" Then
        MsgBox "Please Scan Barcode.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.DTPicker1.Value = "" Then
        MsgBox "Please Enter a date.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.cboTeam.Value = "" Then
        MsgBox "Please Select Team.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.txtTimefrom.Value = "" Then
        MsgBox "Please Key Time From.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.txtTimeto.Value = "" Then
        MsgBox "Please Key Time To.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.cboProblem.Value = "" Then
        MsgBox "Please Select M/C Problem.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.cboModel.Value = "" Then
        MsgBox "Please Select Model.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.txtRepair.Value = "" Then
        MsgBox "Please Key Repair Detail.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.optOK.Value = "" Then
        MsgBox "Please check OK or NG.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.cboNG.Value = "" Then
        MsgBox "Please Key Q'ty NG Setting.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Me.cboBy.Value = "" Then
        MsgBox "Please Key Repair By.", vbExclamation, "Spare Part Change"
        Me.txtSparepartchange.SetFocus
        Exit Sub
    End If
    If Not IsNumeric(Me.cboNumber.Value) Then
        MsgBox "The Amount box must contain a number.", vbExclamation, "Spare Part Change"
        Me.cboNumber.SetFocus
        Exit Sub
    End If
    While Worksheets(2).Cells(temprow, 1) <> "" And Trim(Worksheets(2).Cells(temprow, 1)) <> Me.txtSparepartchange.Text
    temprow = temprow + 1
    Wend
' Write data to worksheet
    RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Sheet1").Range("A1")
        .Offset(RowCount, 0).Value = Me.txtSparepartchange.Value
        .Offset(RowCount, 1).Value = Worksheets(2).Cells(temprow, 2) 'Me.txtCodemodelsizetype.Value
        .Offset(RowCount, 2).Value = Format(DTPicker1.Value, "dd/mm/yyyy")
        .Offset(RowCount, 3).Value = Me.cboTeam.Value
        .Offset(RowCount, 4).Value = Me.txtTimefrom.Value
        .Offset(RowCount, 5).Value = Me.txtTimeto.Value
        .Offset(RowCount, 6).Value = Me.txtTimetotal.Value
        .Offset(RowCount, 7).Value = Me.txtGtotal.Value
        .Offset(RowCount, 8).Value = Me.cboBrother.Value & Me.cboNumber.Value
        .Offset(RowCount, 9).Value = Me.cboProblem.Value
        .Offset(RowCount, 10).Value = Me.cboModel.Value
        .Offset(RowCount, 11).Value = Me.txtRepair.Value
    If Me.optOK.Value Then
        .Offset(RowCount, 12).Value = "OK"
    End If
    If Me.optNG.Value Then
        .Offset(RowCount, 12).Value = "NG"
    End If
        .Offset(RowCount, 13).Value = Me.cboNG.Value
        .Offset(RowCount, 14).Value = Me.cboBy.Value
        .Offset(RowCount, 15).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
    End With
'Clear the form
    For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    End If
Next ctl
End Sub

Code: Select all

Sub TotalMin()
Dim rTotal As Range
Set rTotal = Worksheets("Sheet1").Range("G4")
Do While rTotal.Offset(0, -1) <> ""
    rTotal = (rTotal.Offset(0, -1) - rTotal.Offset(0, -2)) * 24 * 60
        If rTotal.Offset(0, 1).Row <> 4 Then
            rTotal.Offset(0, 1) = rTotal + rTotal.Offset(-1, 1)
        Else
            rTotal.Offset(0, 1) = rTotal
        End If
    Set rTotal = rTotal.Offset(1, 0)
Loop
End Sub