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

[blake3393]

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

[bulina2k]

If you modify the Private Sub UserForm_Initialize_2() to Private Sub UserForm_Initialize() the form will take CurrentUser.

[blake3393]

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.

[LJMetzger]

Hi blake3393,

I fell in love with your file, so I got a little carried away. I hope you don't mind. The attached file should help you get started. If you have any questions and or problems, please feel free to ask.

Major items of interest:
a. Added code to ShowMe()
b. Changed formula on User1 cell E15 to =SUM(O13:O32). The same formula required on the other User Sheets.
c. There was a missing class module for the Date Picker. I replaced the Date Picker with a different Date Picker.
d. There is a small possibility of a problem with US vs European date format.
e. Added a Named Range called 'myHolidays', which contains a list of Holidays.
See Sheet 'Login' Cell 'W8'
f. Changes in Workbook_Open() for 'Named Range'.
g. Major changes to UserForm 'HoidayRequest'.
h. Bad format corrected in Sheet 'Pending Requests' Cell 'D7'.
i. No error checking is done for overlapping dates (date conflicts) with previous requests.
j. Added optional SpinButton for dates (hold down 'Ctrl' key to increment/decrement by MONTH).
k. In UserForm frmLogin Module, Sub cmdCheck_Click(), added putting the focus on the 'First' Sheet the User has access to.

Additional items that may help you:
Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.


It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

04-Sept-2015:
The Calendar Form in the attached file contains an error when used with European data formats. The error is corrected in subsequent files in post #9 and post #10 in this thread.

[blake3393]

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.

[LJMetzger]

Hi Steven,

Thanks for the rep points and the kind words. I made some changes to the Calendar Form in an attempt to make it universal for both sides of the Pond. I did not test the Drop Down portion. When I fix it, I will let you know.

When I change months, I always use the Arrow buttons.

Please feel free to ask if you have any additional problems and/or questions.

Lewis

I just tested the Calendar and I can't duplicate the problem. The problem may be caused by US vs European date format.

[LJMetzger]

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.

Please Login or Register  to view this content.

Lewis

[blake3393]

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

[LJMetzger]

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

[LJMetzger]

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

[blake3393]

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

[LJMetzger]

Hi Steven,

.. 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:

Please Login or Register  to view this content.

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

[blake3393]

Hey lewis.

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

Regards

Steven

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:

Please Login or Register  to view this content.

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

[LJMetzger]

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