Set ws = Worksheets("Future Changes")
irow = 1
'find first empty row on check sheet and then transfers inputs to row
'irow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Sheets("Future Changes").Select
Do Until ws.Cells(irow, 1) = ""
If Staff = CStr(Cells(irow, 1)) Then
MsgBox "A future change " & (cboChange.Text) & " exists for this staff member, press ok to view this change", vbOKOnly
Reply = MsgBox("The existing change is as follows :" & vbNewLine & "Staff No: " & txtStaffNo & vbNewLine & "Name: " & txtFirstName & " " & txtSurname & vbNewLine & "Office: " & cboOffice & " " & cboGrade & vbNewLine & "Working Pattern: " & cboWorkingPattern & " Hours: " & txtHours & " Weeks: " & txtWeeks & " WTE: " & txtWte & vbNewLine & "Notes: " & txtNotes & vbNewLine & "Date Change Effective from " & ws.Cells(i, 12) & vbNewLine & "***If you wish to keep the above change and add a second change for the staff member click yes***" & vbNewLine & "+++If you wish to replace the change shown above with the one you have just entered, please click No+++", vbYesNo)
If Reply = 6 Then
Set ws = Worksheets("Future Changes")
irow = 1
'find first empty row on check sheet and then transfers inputs to row
irow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Do Until ws.Cells(irow, 1) = ""
irow = irow + 1
Loop
Reply = MsgBox("Are you sure you wish to save these changes?", vbYesNo)
If Reply = 6 Then
ws.Cells(irow, 1) = txtStaffNo
ws.Cells(irow, 2) = cboOffice
ws.Cells(irow, 3) = txtFirstName
ws.Cells(irow, 4) = txtSurname
ws.Cells(irow, 5) = cboWorkingPattern
ws.Cells(irow, 6) = txtHours
ws.Cells(irow, 7) = txtWeeks
ws.Cells(irow, 8) = txtWte
ws.Cells(irow, 9) = cboGrade
ws.Cells(irow, 10) = txtNotes
ws.Cells(irow, 11) = cboChange
ws.Cells(irow, 12) = txtDate
ws.Cells(irow, 13) = Delete
Saved = "Yes"
Reply = MsgBox("Your record has now been saved, do you wish to make further changes?", vbYesNo)
If Reply = 6 Then
ThisWorkbook.Save
Unload Me
frmStipInput.Show
Else
ThisWorkbook.Save
ThisWorkbook.Close
End If
End If
Else
ws.Cells(irow, 1) = txtStaffNo
ws.Cells(irow, 2) = cboOffice
ws.Cells(irow, 3) = txtFirstName
ws.Cells(irow, 4) = txtSurname
ws.Cells(irow, 5) = cboWorkingPattern
ws.Cells(irow, 6) = txtHours
ws.Cells(irow, 7) = txtWeeks
ws.Cells(irow, 8) = txtWte
ws.Cells(irow, 9) = cboGrade
ws.Cells(irow, 10) = txtNotes
ws.Cells(irow, 11) = cboChange
ws.Cells(irow, 12) = txtDate
ws.Cells(irow, 13) = Delete
Saved = "Yes"
Reply = MsgBox("Your record has now been saved, do you wish to make further changes?", vbYesNo)
If Reply = 6 Then
ThisWorkbook.Save
Unload Me
frmStipInput.Show
Else
ThisWorkbook.Save
ThisWorkbook.Close
End If
End If
End If
irow = irow + 1
Loop
If Saved = "Yes" Then
Unload Me
frmStipInput.Show
Else
irow = 1
'find first empty row on check sheet and then transfers inputs to row
irow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Do Until ws.Cells(irow, 1) = ""
irow = irow + 1
Loop
Reply = MsgBox("Are you sure you wish to save these changes?", vbYesNo)
If Reply = 6 Then
ws.Cells(irow, 1) = txtStaffNo
ws.Cells(irow, 2) = cboOffice
ws.Cells(irow, 3) = txtFirstName
ws.Cells(irow, 4) = txtSurname
ws.Cells(irow, 5) = cboWorkingPattern
ws.Cells(irow, 6) = txtHours
ws.Cells(irow, 7) = txtWeeks
ws.Cells(irow, 8) = txtWte
ws.Cells(irow, 9) = cboGrade
ws.Cells(irow, 10) = txtNotes
ws.Cells(irow, 11) = cboChange
ws.Cells(irow, 12) = txtDate
ws.Cells(irow, 13) = Delete
Saved = "Yes"
Reply = MsgBox("Your record has now been saved, do you wish to make further changes?", vbYesNo)
If Reply = 6 Then
ThisWorkbook.Save
Unload Me
frmStipInput.Show
Else
ThisWorkbook.Save
ThisWorkbook.Close
End If
End If
End If
End If
End If
Bookmarks