Thread: Getting a userform to populate cells and a copy to autosave to a network folder.

(see attached xls document)

Presently, the user is able to click on "Submit" in the row applicable to the day for which they want to use paid time off. The macro looks at their windows user login, then converts the login into a name, and inserts it into a cell next to the date, and then date/time stamps the cell to the right of their name. If the first cell is already populated with someone's name, then it puts them in subsequent cells that allow for 2 people in a waiting list.

I need to modify this since questions such as (1) what time of PTO they want to use, (2) how much PTO they want to use, and (3) whether or not they request spans multiple days had to be answered.

After looking through the forum, I saw that a UserForm would probably serve my needs more effectively. So, instead of one just clicking on "submit request" to the right of the day and their name appearing in the first (and subsequent) box(s), I want the userform to pop-up when "submit request" is pressed. The following process is desirable:

1. User clicks on "Submit Request" to the right of the date and then the userform pops up.

2. User fills out the userform (I have already made it in the workbook), and then clicks submit. Note: It is desirable that the userform recognize the person based on their windows user login, and prepopulate their name into the form.

3. After clicking "Submit" on the userform, the cell to the right of the date they are requesting populates with their name, and then time stamps it in the cell to the right of their name (as the form is currently setup to do).

4. If the user gave a date range in the userform, then Step 3 happens for each date within the range following the rules in the module seen below.

5. An image or screenshot of just the completed userform gets automatically saved to a specific folder on a network drive.

An example of the code (seen in Module 9 of the attached form) that is currently being employed is as folllows:

Sub requestPTO01()

Dim nm As String
    nm = Environ("username")
      
    Select Case nm
    Case Is = "abc1"
    nm = "Name 1"
    Case Is = "abc2"
    nm = "Name 2"
    Case Is = "abc3"
    nm = "Name 3"
           End Select  
  
    Select Case ""
        Case [D22]
            With [D22]
                .Value = nm
                .Offset(, 1) = Now
                .Offset(, 1).NumberFormat = "mm/dd/yy/hh:mm"
                MsgBox "You are the 1st Request for the day you have requested.  An email has been sent to you confirming the submission of your request.  Your confirmation email is not an approval of the time off requested, but is only confirmation that your request has been submitted and is awaiting approval. Command will respond to your request within 7 calendar days with an approval or denial."
            End With
        Case [J22]
            With [J22]
                .Value = nm
                .Offset(, 1) = Now
                .Offset(, 1).NumberFormat = "mm/dd/yy/hh:mm"
                MsgBox "Your request is denied.  However, you are the 2nd Request for the day you have requested, and have been put on a waiting list.  An email has been sent to you confirming the receipt of your request, and your placement on the waiting list.  If circumstances should allow for your request to be approved at a later date, you will be contacted by email with the confirmation of an approval."
            End With
        Case [P22]
            With [P22]
                .Value = nm
                .Offset(, 1) = Now
                .Offset(, 1).NumberFormat = "mm/dd/yy/hh:mm"
                MsgBox "Your request is denied.  However, you are the 3rd Request for the day you have requested, and have been put on a waiting list.  An email has been sent to you confirming the receipt of your request, and your placement on the waiting list.  If circumstances should allow for your request to be approved at a later date, you will be contacted by email with the confirmation of an approval."
            End With
        Case Else
            MsgBox "Your request is denied and the waiting list is full.  Please check back later to see if your desired date becomes available."
    End Select
                 
End Sub

How about this one? It works for me (I actually use the form in .xlsb, but save it as an .xls file because I know thats what most of you gurus out there use).