how to make a User form that auto-populates info from a selected sheet

Hi there,

firstly id like to say how helpful this forum has been so far, and also apologize if this is already a question on the forum. I have tried searching for what I'm after but I've not found anything, probably down to the way I'm wording the question.

I'm working on a project to help deal with holiday requests and allow users to see what holidays they have taken and remaining days, further to this I also want to implement a way to request future holidays.

The section I am currently working and stuck on is: sending a response to a request using a userform.

my idea is to have a dropdown list or similar to select the individual that is requesting the holiday (each user has their own sheet where the info I want is to be pulled from)

This list then auto completes some parts of the form, such as, name, total holiday allowance and current remaining of this allowance. all this info will be on the selected users sheet. if this is possible i then want this to add the approval to a list on the sheet of the user.

i hope this made sense and if not please let me know and i will do my best to clarify my question. ( this seems to make sense in my head, which probably means it wont make sense to someone else )

ive attached my current workbook if you need to see what i am trying to put in words.

Im using Excel 2013

many thanks in advance

Hi thanks for this tip, however it currently works on this setup however the issue I have relates to the rest of the information I would like to populate based on a drop down selection of users on the form (sheets). Eg user 1 is sheet 1. Does this make sense? Basically I'm after the form to have a drop down box that is a list of the sheets (users) then some of the items on the form then auto populate with the information on the selected sheet/ user... Many thanks again.

Hi Lews,

Firstly I would like to say...... THANK YOU for the reply and all the useful pointers above ^^^

These are some rather amazing changes and very much welcomed. so please... use as you wish!!! I would just ask if you do if you could then chuck something my way as I'm learning as I go. So the more I see the more I can pick up.

just so you know this is my first project and a massive learning experience as I have only just started learning VBA with this project.

I have encountered 1 problem though, it is in relation to the calender you've added.

Using a calender is something I have been attempting for a while and I almost gave up on till now.

However I cant seem to get this one you've added to work correctly. all the months are listed as January and if you click today it changes to today, but when you select a date it resets to January again. am I missing something? I've tried looking at your code but as I said before I'm rather new to this and cannot find where it gets the month list from.

Calender issue.png


I'm still feeding my brain with other bits you've added and altered and again I must say thank you, I'm already learning so much.

Regards,

Steven.

Hi Steven,

As I suspected, the problem seemed to be the difference between US "mm/dd/yy" and European "dd/mm/yy". In the UserForm module CalendarForm, replace UserForm_Initialize() with the following version (changes in red). This will hopefully solve the problem.

Private Sub UserForm_Initialize()
    Dim myDate As Date
    Dim iMonth As Long
    Dim sSeedValue As String
    Application.EnableEvents = False
    sSeedValue = GetCalendarFormSeedDate()
    If Len(sSeedValue) = 0 Then
      ThisDay = Date
    Else
      ThisDay = CDate(sSeedValue)
    End If
    ThisDay = Format(ThisDay, "mmm d, yyyy")
    ThisMth = Format(ThisDay, "mm")
    ThisYear = Format(ThisDay, "yyyy")
    For iMonth = 1 To 12
        myDate = DateSerial(ThisYear, iMonth, 1)
        CB_Mth.AddItem Format(myDate, "mmmm")
    Next
    CB_Mth.ListIndex = (ThisMth) - 1
    For i = -20 To 20
        If i = 1 Then CB_Yr.AddItem Format((ThisDay), "yyyy") Else CB_Yr.AddItem _
            Format((DateAdd("yyyy", (i - 1), ThisDay)), "yyyy")
    Next
    CB_Yr.ListIndex = 21
    CreateCal = True
    ThisButton = ThisDay
    Call Build_Calendar
    HelpLabel.Caption = Format(ThisDay, "mmm d, yyyy")
    Application.EnableEvents = True
End Sub

Lewis

Hi Lewis,

this worked a treat on the calender

now time to look at the Response form.... lets see what happens when i try practice what i understood from the request form code haha... we shall see but thanks again for what you have done so far for me.

but do feel free to do whatever you wish to the file. i will probably return soon when i get stuck anyway...

again thanks for the great help.

Steven

Hi Steven,

...but do feel free to do whatever you wish to the file.

Take the attached file for a Test Drive. I hope I didn't interfere with anything you were working on. When adding Start and End Date try adding dates in the range of November 1 thru November 15 (0 days remaining) or September 1 thru September 10 (have request pending for 'User 1').

Summary of the changes I made:
a. Added Updated Calendar Form with arrows to increment/decrement by day or month.
b. Added a Manager Line Item on each User Sheet.
c. Added Application.Quit in frmlogin cmdCheck_Click().
d. Modifed HolidayRequest UserForm Code extensively to upgrade and correct errors.
e. Added ModfrmUserAddDeleteEtc to add, modify, or delete users with skeleton code - skeleton being filled in.
f. Added code to initialize, update, and undo changes to 'Availability' Sheet.
For example, when reviewer selects approved, the following functions would be called:
(1) CheckDateAvailability()
(2) CheckDatesForAvailabilityConflicts()
(3) UpdateAvailabilitySheetData()
When a User wanted to cancel an approved request, the Manager would select the request to UNDO and the following functions would be called:
(1) CheckDateAvailability() with the 'UNDO' option selected to verify the integrity of the 'Availability' Sheet.
(2) UpdateAvailabilitySheetData() with the option to remove 'Days Taken'.
g. Added test for User 'Pending' Conflicts with Self ('Pending Request' and new request for the same date).
h. Started working on User Conflicts with Others ('Pending Request(s) by others for the same date, plus User request would have too many users on simultaneous Holiday).
i. Added required Login when Workbook opens (see ThisWorkbook code module).
NOTE: Sheet 'User 1' was the only sheet used for Testing. Other Users were ignored.

Test Data:
November 1 thru November 15 have 0 days remaining.
September 1 thru September 10 have request pending for 'User 1'.

Suggestions:
a. Add Manager Name to User Page (I added this so I could test).
b. On User Page divide 'Date' column into two columns (Start Date and End Date).
c. Add an 'Availability History' sheet that would log all changes to 'Availability' (e.g. User Name, Start and End dates, and date and time change made to 'Availability'.)
Availability' sheet is a candidate for corruption due to accidental changes, and a log could be used to restore integrity if a disaster were to occur.

Lewis

Hi again Steven,

You have an excellent spreadsheet design. It is well thought out and easy to work with. I am not planning on making any additional changes, unless you have a specific request.

I made the following additional changes to the file (see the attachment)):
a. Added frmLogin Splash Screen to replace MsgBox.
b. Added Remove Unapproved Request (from User Sheet and from 'Pending Requests' Sheet).
c. Added Application.EnableEvents and Application.ScreenUpdating code to improve speed and reduce screen flicker.
d. Update Holiday Request Code (Request Holiday NEW) - to include extensive error checking for:
(1) Date not on Sheet 'AVAILABILITY'
(2) Previous request for the same date(s) by the Requestor.
(3) Potential Date Conflict for unapproved 'Pending Requests' with other users that would cause 'Days Remaining' for a day to become NEGATIVE.
e. Fully implemented Add, Modify, Delete User (from ADMIN sheet).
f. Added ability for User to remove 'Unapproved Requests' from User Sheet and from 'Pending Results' Sheet.
g. Updated Workbook_Open() code.
h. If User is logged in, requests do not required reentry of Password.

If have any problems or question feel free to ask. I tried my best to make the things I did understandable.

Lewis

Hi lewis,

Sorry that I've not posted a reply recently had a few things up in the air.

firstly I want to thank you again for all this your doing / have done... you have done things I really wanted to implement but did not have the knowledge to do so.

what I'm going to do for now is mark this as resolved as you have gone above and beyond my initial question. I will continue as best I can and if I hit another wall am I okay to message you or add another post?

also feel free to keep this if you want.

thanks again for all you have done....... I have along way to go to learn all this but you have given me a great starting point if only I could add more rep I would!!!!!!!

regards,

steven

Hi Steven,

Originally Posted by blake3393

.. I was going through it and cannot work out how to change the pending request into approved.

Can i ask who and how do the pending requests get authorized? And how this reflects on the sheets?

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

Hey lewis.

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

Regards

Steven

Originally Posted by LJMetzger

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

Hi Steven,

There is no rush. I know how long it takes, especially the first time. You learn a lot more when you figure it out yourself. I'll do what I can to help you out if you get stuck.

Lewis