Option Explicit
Private Sub frmPlanning_Initialize()
DTPicker1a.Value = Date
MsgBox = Date
End Sub
' *** PLANNING form SHIFT 2 DETAIL ***
' *** If Copy same address details is selected copy from previous shift ***
Private Sub ChkCopy2_Click()
If ChkCopy2 = "True" Then
txt1Shift2 = txt1Shift1
txt2Shift2 = txt2Shift1
txt3Shift2 = txt3Shift1
txt4Shift2 = txt4Shift1
txtPostShift2 = txtPostShift1
End If
End Sub
' *** PLANNING form SHIFT 3 DETAIL ***
' *** If Copy same address details is selected copy from previous shift ***
Private Sub chkCopy3_Click()
If chkCopy3 = "True" Then
txt1Shift3 = txt1Shift2
txt2Shift3 = txt2Shift2
txt3Shift3 = txt3Shift2
txt4Shift3 = txt4Shift2
txtPostShift3 = txtPostShift2
End If
End Sub
' *** PLANNING form SHIFT 4 DETAIL ***
' *** If Copy same address details is selected copy from previous shift ***
Private Sub chkCopy4_Click()
If chkCopy4 = "True" Then
txt1Shift4 = txt1Shift3
txt2Shift4 = txt2Shift3
txt3Shift4 = txt3Shift3
txt4Shift4 = txt4Shift3
txtPostShift4 = txtPostShift3
End If
End Sub
' *** PLANNING form SHIFT 5 DETAIL ***
' *** If Copy same address details is selected copy from previous shift ***
Private Sub chkCopy5_Click()
If chkCopy5 = "True" Then
txt1Shift5 = txt1Shift4
txt2Shift5 = txt2Shift4
txt3Shift5 = txt3Shift4
txt4Shift5 = txt4Shift4
txtPostShift5 = txtPostShift4
End If
End Sub
' *** PLANNING form SHIFT 6 DETAIL ***
' *** If Copy same address details is selected copy from previous shift ***
Private Sub chkCopy6_Click()
If chkCopy6 = "True" Then
txt1Shift6 = txt1Shift5
txt2Shift6 = txt2Shift5
txt3Shift6 = txt3Shift5
txt4Shift6 = txt4Shift5
txtPostShift6 = txtPostShift5
End If
End Sub
' *** PLANNING form SHIFT 7 DETAIL ***
' *** If Copy same address details is selected copy from previous shift ***
Private Sub chkCopy7_Click()
If chkCopy7 = "True" Then
txt1Shift7 = txt1Shift6
txt2Shift7 = txt2Shift6
txt3Shift7 = txt3Shift6
txt4Shift7 = txt4Shift6
txtPostShift7 = txtPostShift6
End If
End Sub
Private Sub lstAllocated_Click()
lstAllocated.SetFocus
End Sub
' ***** PLANNING form CORE DETAIL *****
' ***** Operates a Vlookup function that upon selection of a core machine
' populates the attachment text boxes *****
Private Sub lstMachine_Change()
Dim Machine As String
Dim datarange As Range
Application.ScreenUpdating = False
Machine = lstMachine.Value
Sheets("RefTables").Select
Set datarange = Range("R:Z")
txtAtt1.Value = Application.WorksheetFunction.VLookup(Machine, datarange, 2, False)
txtAtt2.Value = Application.WorksheetFunction.VLookup(Machine, datarange, 3, False)
txtAtt3.Value = Application.WorksheetFunction.VLookup(Machine, datarange, 4, False)
txtAtt4.Value = Application.WorksheetFunction.VLookup(Machine, datarange, 5, False)
txtAtt5.Value = Application.WorksheetFunction.VLookup(Machine, datarange, 6, False)
txtAtt6.Value = Application.WorksheetFunction.VLookup(Machine, datarange, 7, False)
txtAtt7.Value = Application.WorksheetFunction.VLookup(Machine, datarange, 8, False)
txtAtt8.Value = Application.WorksheetFunction.VLookup(Machine, datarange, 9, False)
End Sub
Private Sub MultiPage2_Change()
End Sub
Private Sub MultiPage1_Change()
End Sub
'***** PLANNING form SUBMIT ******
'**** Writes back planned shifts to Planning data sheet *****
Private Sub SubmitPlan_Click()
' *** Find last available row to post data to ***
' *** Post Back Core Details ***
Sheets("Planning Sheet").Select
Range("B3").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell.Value = "Core"
ActiveCell.Offset(0, 1).Value = frmPlanning.lstAllocated.Value
ActiveCell.Offset(0, 2).Value = frmPlanning.lstMachine.Value
ActiveCell.Offset(0, 3).Value = frmPlanning.txtAtt1.Value
ActiveCell.Offset(0, 4).Value = frmPlanning.txtAtt2.Value
ActiveCell.Offset(0, 5).Value = frmPlanning.txtAtt3.Value
ActiveCell.Offset(0, 6).Value = frmPlanning.txtAtt4.Value
ActiveCell.Offset(0, 7).Value = frmPlanning.txtAtt5.Value
ActiveCell.Offset(0, 8).Value = frmPlanning.txtAtt6.Value
ActiveCell.Offset(0, 9).Value = frmPlanning.txtAtt7.Value
ActiveCell.Offset(0, 10).Value = frmPlanning.txtAtt8.Value
' *** Post back Shift 1 Details ***
ActiveCell.Offset(0, 11).Value = frmPlanning.DTPicker1a.Value
ActiveCell.Offset(0, 12).Value = frmPlanning.DTPicker1b.Value
ActiveCell.Offset(0, 13).Value = frmPlanning.DTPicker1c.Value
' ** Offset 14 is a calculated field**
ActiveCell.Offset(0, 15).Value = frmPlanning.lblShift1
ActiveCell.Offset(0, 16).Value = frmPlanning.txt1Shift1.Value
ActiveCell.Offset(0, 17).Value = frmPlanning.txt2Shift1.Value
ActiveCell.Offset(0, 18).Value = frmPlanning.txt3Shift1.Value
ActiveCell.Offset(0, 19).Value = frmPlanning.txt4Shift1.Value
ActiveCell.Offset(0, 20).Value = frmPlanning.txtPostShift1.Value
' *** Post back Shift 1 Contact Details ***
ActiveCell.Offset(0, 21).Value = frmPlanning.txtConName1.Value
ActiveCell.Offset(0, 22).Value = frmPlanning.txtPhone1.Value
ActiveCell.Offset(0, 23).Value = frmPlanning.chkCont1.Value
ActiveCell.Offset(0, 24).Value = frmPlanning.txtDesc1.Value
' *** Post back Shift 1 Transport Details ***
ActiveCell.Offset(0, 25).Value = frmPlanning.txtTrans1.Value
ActiveCell.Offset(0, 26).Value = frmPlanning.txtFrom1.Value
ActiveCell.Offset(0, 27).Value = frmPlanning.txtTo1.Value
ActiveCell.Offset(0, 28).Value = frmPlanning.txtMile1.Value
' *** Find last available row to post data to ***
' *** Post Back Core Details ***
Sheets("Planning Sheet").Select
Range("B3").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell.Value = "Core"
ActiveCell.Offset(0, 1).Value = frmPlanning.lstAllocated.Value
ActiveCell.Offset(0, 2).Value = frmPlanning.lstMachine.Value
ActiveCell.Offset(0, 3).Value = frmPlanning.txtAtt1.Value
ActiveCell.Offset(0, 4).Value = frmPlanning.txtAtt2.Value
ActiveCell.Offset(0, 5).Value = frmPlanning.txtAtt3.Value
ActiveCell.Offset(0, 6).Value = frmPlanning.txtAtt4.Value
ActiveCell.Offset(0, 7).Value = frmPlanning.txtAtt5.Value
ActiveCell.Offset(0, 8).Value = frmPlanning.txtAtt6.Value
ActiveCell.Offset(0, 9).Value = frmPlanning.txtAtt7.Value
ActiveCell.Offset(0, 10).Value = frmPlanning.txtAtt8.Value
' *** Post back Shift 2 Details ***
ActiveCell.Offset(0, 11).Value = frmPlanning.DTPicker2a.Value
ActiveCell.Offset(0, 12).Value = frmPlanning.DTPicker2b.Value
ActiveCell.Offset(0, 13).Value = frmPlanning.DTPicker2c.Value
' ** Offset 14 is a calculated field**
ActiveCell.Offset(0, 15).Value = frmPlanning.lblShift2
ActiveCell.Offset(0, 16).Value = frmPlanning.txt1Shift2.Value
ActiveCell.Offset(0, 17).Value = frmPlanning.txt2Shift2.Value
ActiveCell.Offset(0, 18).Value = frmPlanning.txt3Shift2.Value
ActiveCell.Offset(0, 19).Value = frmPlanning.txt4Shift2.Value
ActiveCell.Offset(0, 20).Value = frmPlanning.txtPostShift2.Value
' *** Post back Shift 2 Contact Details ***
ActiveCell.Offset(0, 21).Value = frmPlanning.txtConName2.Value
ActiveCell.Offset(0, 22).Value = frmPlanning.txtPhone2.Value
ActiveCell.Offset(0, 23).Value = frmPlanning.chkCont2.Value
ActiveCell.Offset(0, 24).Value = frmPlanning.txtDesc2.Value
' *** Post back Shift 2 Transport Details ***
ActiveCell.Offset(0, 25).Value = frmPlanning.txtTrans2.Value
ActiveCell.Offset(0, 26).Value = frmPlanning.txtFrom2.Value
ActiveCell.Offset(0, 27).Value = frmPlanning.txtTo2.Value
ActiveCell.Offset(0, 28).Value = frmPlanning.txtMile2.Value
' *** Find last available row to post data to ***
' *** Post Back Core Details ***
Sheets("Planning Sheet").Select
Range("B3").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell.Value = "Core"
ActiveCell.Offset(0, 1).Value = frmPlanning.lstAllocated.Value
ActiveCell.Offset(0, 2).Value = frmPlanning.lstMachine.Value
ActiveCell.Offset(0, 3).Value = frmPlanning.txtAtt1.Value
ActiveCell.Offset(0, 4).Value = frmPlanning.txtAtt2.Value
ActiveCell.Offset(0, 5).Value = frmPlanning.txtAtt3.Value
ActiveCell.Offset(0, 6).Value = frmPlanning.txtAtt4.Value
ActiveCell.Offset(0, 7).Value = frmPlanning.txtAtt5.Value
ActiveCell.Offset(0, 8).Value = frmPlanning.txtAtt6.Value
ActiveCell.Offset(0, 9).Value = frmPlanning.txtAtt7.Value
ActiveCell.Offset(0, 10).Value = frmPlanning.txtAtt8.Value
' *** Post back Shift 3 Details ***
ActiveCell.Offset(0, 11).Value = frmPlanning.DTPicker3a.Value
ActiveCell.Offset(0, 12).Value = frmPlanning.DTPicker3b.Value
ActiveCell.Offset(0, 13).Value = frmPlanning.DTPicker3c.Value
' ** Offset 14 is a calculated field**
ActiveCell.Offset(0, 15).Value = frmPlanning.lblShift3
ActiveCell.Offset(0, 16).Value = frmPlanning.txt1Shift3.Value
ActiveCell.Offset(0, 17).Value = frmPlanning.txt2Shift3.Value
ActiveCell.Offset(0, 18).Value = frmPlanning.txt3Shift3.Value
ActiveCell.Offset(0, 19).Value = frmPlanning.txt4Shift3.Value
ActiveCell.Offset(0, 20).Value = frmPlanning.txtPostShift3.Value
' *** Post back Shift 3 Contact Details ***
ActiveCell.Offset(0, 21).Value = frmPlanning.txtConName3.Value
ActiveCell.Offset(0, 22).Value = frmPlanning.txtPhone3.Value
ActiveCell.Offset(0, 23).Value = frmPlanning.chkCont3.Value
ActiveCell.Offset(0, 24).Value = frmPlanning.txtDesc3.Value
' *** Post back Shift 3 Transport Details ***
ActiveCell.Offset(0, 25).Value = frmPlanning.txtTrans3.Value
ActiveCell.Offset(0, 26).Value = frmPlanning.txtFrom3.Value
ActiveCell.Offset(0, 27).Value = frmPlanning.txtTo3.Value
ActiveCell.Offset(0, 28).Value = frmPlanning.txtMile3.Value
' *** Find last available row to post data to ***
' *** Post Back Core Details ***
Sheets("Planning Sheet").Select
Range("B3").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell.Value = "Core"
ActiveCell.Offset(0, 1).Value = frmPlanning.lstAllocated.Value
ActiveCell.Offset(0, 2).Value = frmPlanning.lstMachine.Value
ActiveCell.Offset(0, 3).Value = frmPlanning.txtAtt1.Value
ActiveCell.Offset(0, 4).Value = frmPlanning.txtAtt2.Value
ActiveCell.Offset(0, 5).Value = frmPlanning.txtAtt3.Value
ActiveCell.Offset(0, 6).Value = frmPlanning.txtAtt4.Value
ActiveCell.Offset(0, 7).Value = frmPlanning.txtAtt5.Value
ActiveCell.Offset(0, 8).Value = frmPlanning.txtAtt6.Value
ActiveCell.Offset(0, 9).Value = frmPlanning.txtAtt7.Value
ActiveCell.Offset(0, 10).Value = frmPlanning.txtAtt8.Value
' *** Post back Shift 4 Details ***
ActiveCell.Offset(0, 11).Value = frmPlanning.DTPicker4a.Value
ActiveCell.Offset(0, 12).Value = frmPlanning.DTPicker4b.Value
ActiveCell.Offset(0, 13).Value = frmPlanning.DTPicker4c.Value
' ** Offset 14 is a calculated field**
ActiveCell.Offset(0, 15).Value = frmPlanning.lblShift4
ActiveCell.Offset(0, 16).Value = frmPlanning.txt1Shift4.Value
ActiveCell.Offset(0, 17).Value = frmPlanning.txt2Shift4.Value
ActiveCell.Offset(0, 18).Value = frmPlanning.txt3Shift4.Value
ActiveCell.Offset(0, 19).Value = frmPlanning.txt4Shift4.Value
ActiveCell.Offset(0, 20).Value = frmPlanning.txtPostShift4.Value
' *** Post back Shift 4 Contact Details ***
ActiveCell.Offset(0, 21).Value = frmPlanning.txtConName4.Value
ActiveCell.Offset(0, 22).Value = frmPlanning.txtPhone4.Value
ActiveCell.Offset(0, 23).Value = frmPlanning.chkCont4.Value
ActiveCell.Offset(0, 24).Value = frmPlanning.txtDesc4.Value
' *** Post back Shift 4 Transport Details ***
ActiveCell.Offset(0, 25).Value = frmPlanning.txtTrans4.Value
ActiveCell.Offset(0, 26).Value = frmPlanning.txtFrom4.Value
ActiveCell.Offset(0, 27).Value = frmPlanning.txtTo4.Value
ActiveCell.Offset(0, 28).Value = frmPlanning.txtMile4.Value
' *** Find last available row to post data to ***
' *** Post Back Core Details ***
Sheets("Planning Sheet").Select
Range("B3").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell.Value = "Core"
ActiveCell.Offset(0, 1).Value = frmPlanning.lstAllocated.Value
ActiveCell.Offset(0, 2).Value = frmPlanning.lstMachine.Value
ActiveCell.Offset(0, 3).Value = frmPlanning.txtAtt1.Value
ActiveCell.Offset(0, 4).Value = frmPlanning.txtAtt2.Value
ActiveCell.Offset(0, 5).Value = frmPlanning.txtAtt3.Value
ActiveCell.Offset(0, 6).Value = frmPlanning.txtAtt4.Value
ActiveCell.Offset(0, 7).Value = frmPlanning.txtAtt5.Value
ActiveCell.Offset(0, 8).Value = frmPlanning.txtAtt6.Value
ActiveCell.Offset(0, 9).Value = frmPlanning.txtAtt7.Value
ActiveCell.Offset(0, 10).Value = frmPlanning.txtAtt8.Value
' *** Post back Shift 5 Details ***
ActiveCell.Offset(0, 11).Value = frmPlanning.DTPicker5a.Value
ActiveCell.Offset(0, 12).Value = frmPlanning.DTPicker5b.Value
ActiveCell.Offset(0, 13).Value = frmPlanning.DTPicker5c.Value
' ** Offset 14 is a calculated field**
ActiveCell.Offset(0, 15).Value = frmPlanning.lblShift5
ActiveCell.Offset(0, 16).Value = frmPlanning.txt1Shift5.Value
ActiveCell.Offset(0, 17).Value = frmPlanning.txt2Shift5.Value
ActiveCell.Offset(0, 18).Value = frmPlanning.txt3Shift5.Value
ActiveCell.Offset(0, 19).Value = frmPlanning.txt4Shift5.Value
ActiveCell.Offset(0, 20).Value = frmPlanning.txtPostShift5.Value
' *** Post back Shift 5 Contact Details ***
ActiveCell.Offset(0, 21).Value = frmPlanning.txtConName5.Value
ActiveCell.Offset(0, 22).Value = frmPlanning.txtPhone5.Value
ActiveCell.Offset(0, 23).Value = frmPlanning.chkCont5.Value
ActiveCell.Offset(0, 24).Value = frmPlanning.txtDesc5.Value
' *** Post back Shift 5 Transport Details ***
ActiveCell.Offset(0, 25).Value = frmPlanning.txtTrans5.Value
ActiveCell.Offset(0, 26).Value = frmPlanning.txtFrom5.Value
ActiveCell.Offset(0, 27).Value = frmPlanning.txtTo5.Value
ActiveCell.Offset(0, 28).Value = frmPlanning.txtMile5.Value
' *** Find last available row to post data to ***
' *** Post Back Core Details ***
Sheets("Planning Sheet").Select
Range("B3").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell.Value = "Core"
ActiveCell.Offset(0, 1).Value = frmPlanning.lstAllocated.Value
ActiveCell.Offset(0, 2).Value = frmPlanning.lstMachine.Value
ActiveCell.Offset(0, 3).Value = frmPlanning.txtAtt1.Value
ActiveCell.Offset(0, 4).Value = frmPlanning.txtAtt2.Value
ActiveCell.Offset(0, 5).Value = frmPlanning.txtAtt3.Value
ActiveCell.Offset(0, 6).Value = frmPlanning.txtAtt4.Value
ActiveCell.Offset(0, 7).Value = frmPlanning.txtAtt5.Value
ActiveCell.Offset(0, 8).Value = frmPlanning.txtAtt6.Value
ActiveCell.Offset(0, 9).Value = frmPlanning.txtAtt7.Value
ActiveCell.Offset(0, 10).Value = frmPlanning.txtAtt8.Value
' *** Post back Shift 6 Details ***
ActiveCell.Offset(0, 11).Value = frmPlanning.DTPicker6a.Value
ActiveCell.Offset(0, 12).Value = frmPlanning.DTPicker6b.Value
ActiveCell.Offset(0, 13).Value = frmPlanning.DTPicker6c.Value
' ** Offset 14 is a calculated field**
ActiveCell.Offset(0, 15).Value = frmPlanning.lblShift6
ActiveCell.Offset(0, 16).Value = frmPlanning.txt1Shift6.Value
ActiveCell.Offset(0, 17).Value = frmPlanning.txt2Shift6.Value
ActiveCell.Offset(0, 18).Value = frmPlanning.txt3Shift6.Value
ActiveCell.Offset(0, 19).Value = frmPlanning.txt4Shift6.Value
ActiveCell.Offset(0, 20).Value = frmPlanning.txtPostShift6.Value
' *** Post back Shift 6 Contact Details ***
ActiveCell.Offset(0, 21).Value = frmPlanning.txtConName6.Value
ActiveCell.Offset(0, 22).Value = frmPlanning.txtPhone6.Value
ActiveCell.Offset(0, 23).Value = frmPlanning.chkCont6.Value
ActiveCell.Offset(0, 24).Value = frmPlanning.txtDesc6.Value
' *** Post back Shift 6 Transport Details ***
ActiveCell.Offset(0, 25).Value = frmPlanning.txtTrans6.Value
ActiveCell.Offset(0, 26).Value = frmPlanning.txtFrom6.Value
ActiveCell.Offset(0, 27).Value = frmPlanning.txtTo6.Value
ActiveCell.Offset(0, 28).Value = frmPlanning.txtMile6.Value
' *** Find last available row to post data to ***
' *** Post Back Core Details ***
Sheets("Planning Sheet").Select
Range("B3").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell.Value = "Core"
ActiveCell.Offset(0, 1).Value = frmPlanning.lstAllocated.Value
ActiveCell.Offset(0, 2).Value = frmPlanning.lstMachine.Value
ActiveCell.Offset(0, 3).Value = frmPlanning.txtAtt1.Value
ActiveCell.Offset(0, 4).Value = frmPlanning.txtAtt2.Value
ActiveCell.Offset(0, 5).Value = frmPlanning.txtAtt3.Value
ActiveCell.Offset(0, 6).Value = frmPlanning.txtAtt4.Value
ActiveCell.Offset(0, 7).Value = frmPlanning.txtAtt5.Value
ActiveCell.Offset(0, 8).Value = frmPlanning.txtAtt6.Value
ActiveCell.Offset(0, 9).Value = frmPlanning.txtAtt7.Value
ActiveCell.Offset(0, 10).Value = frmPlanning.txtAtt8.Value
' *** Post back Shift 7 Details ***
ActiveCell.Offset(0, 11).Value = frmPlanning.DTPicker7a.Value
ActiveCell.Offset(0, 12).Value = frmPlanning.DTPicker7b.Value
ActiveCell.Offset(0, 13).Value = frmPlanning.DTPicker7c.Value
' ** Offset 14 is a calculated field**
ActiveCell.Offset(0, 15).Value = frmPlanning.lblShift7
ActiveCell.Offset(0, 16).Value = frmPlanning.txt1Shift7.Value
ActiveCell.Offset(0, 17).Value = frmPlanning.txt2Shift7.Value
ActiveCell.Offset(0, 18).Value = frmPlanning.txt3Shift7.Value
ActiveCell.Offset(0, 19).Value = frmPlanning.txt4Shift7.Value
ActiveCell.Offset(0, 20).Value = frmPlanning.txtPostShift7.Value
' *** Post back Shift 7 Contact Details ***
ActiveCell.Offset(0, 21).Value = frmPlanning.txtConName7.Value
ActiveCell.Offset(0, 22).Value = frmPlanning.txtPhone7.Value
ActiveCell.Offset(0, 23).Value = frmPlanning.chkCont7.Value
ActiveCell.Offset(0, 24).Value = frmPlanning.txtDesc7.Value
' *** Post back Shift 7 Transport Details ***
ActiveCell.Offset(0, 25).Value = frmPlanning.txtTrans7.Value
ActiveCell.Offset(0, 26).Value = frmPlanning.txtFrom7.Value
ActiveCell.Offset(0, 27).Value = frmPlanning.txtTo7.Value
ActiveCell.Offset(0, 28).Value = frmPlanning.txtMile7.Value
MsgBox " Submittion complete"
End Sub
'***** PLANNING form RETURN TO MAIN MENU ******
Private Sub CommandButton2_Click()
Unload frmPlanning
Sheets("Menu").Select
End Sub
Private Sub UserForm_Click()
End Sub