Hey lewis.

I'm working on it at the moment. Ill let you know if i hit any bumbs along the way

Regards

Steven

Quote Originally Posted by LJMetzger View Post
Hi Steven,



The mechanics are easy, the design is up to you. I didn't go further into the 'Approved' logic because I didn't know what you wanted, and there was no place to put some of the data in your 'Holiday Response' UserForm.

First of all, we need to know what the fields on the 'User' Sheet should be:
a. DATE - I would prefer two columns one for Start Date and one for End Data, but workable as is.
b. TYPE - ?????
c. AMOUNT - Self Explanatory
d. APPROVED - Is this 'Yes', 'No', 'Pending'? and/or the Manager who approved/rejected?
e. TAKEN - Is this 'Yes', 'No', BLANK, or the Number of Days. In either case, when does this get updated?

Next we need to know what the fields in the 'Holiday Response' UserForm mean, and where the data should go after approval/rejection. For example, where do the 'Comments' go.

---------------
Here is how I see the design, if it were my project.
a. I would add another sheet for Approved/Rejected Requests similar to the 'Pending Requests'.
b. The 'Holiday Response' UserForm would be used to:
(1) Approve Requests.

b. The 'Holiday Response' UserForm would look like it is now, with the addition of a ListBox that would contain the 'Pending Requests' (or 'Approved Requests' if an approved request needed UNDO [i.e. the User changed his mind])

For a specific request,each 'Holiday Response' SUBMIT would (as required):
a. Add/update the 'Approved/Rejected' Sheet data (including updating of comments).
b. Remove the data line from the 'Pending Requests'.
c. Update the 'Approved' Field on the 'User Sheet'.
d. Update the 'AVAILABILITY' Sheet (Days Taken) with code similar to:
Sub TestAvailability()

  Dim myStartDate As Date
  Dim myEndDate As Date
  Dim s As String
  

  myEndDate = CDate("Jun 30, 2015")
  myStartDate = CDate("Jun 15, 2015")
  
  'Update CODE
  s = CheckDateAvailability("DO", myStartDate, myEndDate)
  Debug.Print s

  If Len(s) = 0 Then
    Call UpdateAvailabilitySheetData(myStartDate, myEndDate, 1)
  Else
    MsgBox s
  End If

  'UNDO CODE
  s = CheckDateAvailability("UNDO", myStartDate, myEndDate)
  Debug.Print s

  If Len(s) = 0 Then
    Call UpdateAvailabilitySheetData(myStartDate, myEndDate, -1)
  Else
    MsgBox s
  End If

End Sub
If you don't want to have all the fun yourself, after you update the design, I'll be glad to do whatever you want me to do.

Lewis