Option Explicit
Dim TCO As String
Dim Originator As String
Dim InDate As String
Dim EffectFrom As String
Dim EffectTo As String
Dim Status As String
Dim Part As String
Dim WO As String
Dim LotNum As String
Dim Details As String
Dim Reason As String
Dim i As Integer
Dim Check As Boolean
Dim DayNow As String
Dim ExpDate As String
Dim t As Integer
Dim r As Integer
Dim Date2 As String
Private Sub cmdClear_Click()
txtTCO.Value = ""
txtOriginator.Value = ""
txtInDate.Value = Date
txtEffectFrom.Value = ""
txtEffectTo.Value = ""
txtStatus.Value = "OPEN"
txtPart.Value = ""
txtWO.Value = ""
txtLotNum.Value = ""
txtDetails.Value = ""
txtReason.Value = ""
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub cmdLog_Click()
If txtOriginator.Value = "" Or txtPart = "" Or txtWO = "" Or txtLotNum = "" Or txtDetails = "" Or txtReason = "" Or txtTCO = "" Then
Call sbMsgBoxWarning
Else
'Assign Variables to Input Fields
TCO = txtTCO
Originator = txtOriginator
InDate = txtInDate
EffectFrom = txtEffectFrom
EffectTo = txtEffectTo
Status = txtStatus
Part = txtPart
WO = txtWO
LotNum = txtLotNum
Details = txtDetails
Reason = txtReason
'Set Conditions for "Check" variable
ActiveCell.Select
If ActiveCell = "" Then
Check = True
ElseIf ActiveCell <> "" Then
Check = False
End If
If Check = True Then
i = ActiveCell.Row
ElseIf Check = False Then
i = i + 1
End If
'write data to sheet
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 1).Value = TCO
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 2).Value = Originator
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 3).Value = InDate
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 4).Value = EffectFrom
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 5).Value = EffectTo
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 6).Value = Status
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 7).Value = Part
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 8).Value = WO
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 9).Value = LotNum
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 10).Value = Details
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 11).Value = Reason
'clear fields after entry
txtTCO.Value = ""
txtOriginator.Value = ""
txtInDate.Value = Date
txtEffectFrom.Value = ""
txtEffectTo.Value = ""
txtStatus.Value = "OPEN"
txtPart.Value = ""
txtWO.Value = ""
txtLotNum.Value = ""
txtDetails.Value = ""
txtReason.Value = ""
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
End If
End Sub
Private Sub cmdPrint_Click()
frmPrint.Show
ActiveWorkbook.Worksheets("Print Layout").Cells(3, 7).Value = Date
ActiveWorkbook.Worksheets("Print Layout").Cells(7, 7).Value = txtInDate.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(7, 3).Value = txtTCO.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(10, 2).Value = txtEffectFrom.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(10, 6).Value = txtEffectTo.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(12, 3).Value = txtOriginator.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(14, 3).Value = txtPart.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(15, 3).Value = txtWO.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(16, 3).Value = txtLotNum.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(20, 2).Value = txtDetails.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(26, 2).Value = txtReason.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(15, 7).Value = txtStatus.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(42, 8).Value = txtTCO.Value
End Sub
Private Sub cmdToday_Click()
txtInDate.Value = DayNow
txtEffectFrom.Value = DayNow
txtEffectTo.Value = DateAdd("m", 6, DayNow)
End Sub
Private Sub cmdNextR_Click()
Dim n As Integer
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
n = ActiveCell.Row
txtTCO.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 1).Value
txtOriginator.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 2).Value
txtInDate.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 3).Value
txtEffectFrom.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 4).Value
txtEffectTo.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 5).Value
txtStatus.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 6).Value
txtPart.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 7).Value
txtWO.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 8).Value
txtLotNum.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 9).Value
txtDetails.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 10).Value
txtReason.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 11).Value
End Sub
Private Sub cmdPrevR_Click()
Dim p As Integer
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
p = ActiveCell.Row
txtTCO.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 1).Value
txtOriginator.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 2).Value
txtInDate.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 3).Value
txtEffectFrom.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 4).Value
txtEffectTo.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 5).Value
txtStatus.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 6).Value
txtPart.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 7).Value
txtWO.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 8).Value
txtLotNum.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 9).Value
txtDetails.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 10).Value
txtReason.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 11).Value
End Sub
Private Sub UserForm_Initialize()
r = Worksheets("TCO Log").UsedRange.Rows.Count
r = r + 1
ActiveWorkbook.Worksheets("TCO Log").Cells(r, 1).Activate
txtStatus.Value = "OPEN"
DayNow = Date
DayNow = Format(Date, "dd/mm/yyyy")
txtInDate.Value = DayNow
txtEffectFrom.Value = DayNow
Date2 = Format(Date, "dd/mm/yyyy")
Date2 = DateAdd("m", 6, DayNow)
txtEffectTo.Value = Date2
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
t = ActiveCell.Value
txtTCO.Value = t + 1
txtOriginator = Environ$("Username")
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
End Sub
Public Sub sbMsgBoxWarning()
MsgBox "One of the fields has been left blank", vbQuestion, "Fields Incomplete"
End Sub