Using a userform Calendar to fill in an excel calendar, and also a log

Going to try to state this simple? I created a user form with 2 fields, Name and Date,
I want it to display the information in 2 seperate places. First, a Log. Second, I want their name to be displayed on the calendar.

Basically it is for vacation, when someone request "Paid Time Off", I would like to click Submit Entry on the PTO Log, and have it show the User Form, Input Data, there, and then record it to the Calendar, below the Correct Date...

Can this be done, the way it is currently formatted?

In the Future, I would like the submit Entry tab to be located on every Sheet, connecting to the User Form, but created this for illustrative purposes. Note: Calendar is already built in and active...

Thanks....

Hello 00Able,

The attached workbook has be changed to enter the PTO information with the frmPTOlog. I also added a macro to remove the Close X box on the UserForm. There is one thing that annoys me greatly. - having the Close X visible and being told to use the button! I am sure that I am not alone. The attached workbook has all the corrections made.

There is one thing that annoys me greatly. - having the Close X visible and being told to use the button!

Thank You, it annoyed me too. I don't know why I had it there...

Ok, so i looked at it today and was trying to think of everything that would be a potential issue.

Here is what I came up with...
What if I wanted to know what department the employee was in, so I didn't overbook?
So I added a Department field.

What if the Employee wants several days in a row off, a typical vacation week?
So I added a Start and an End Date.

What if the Employee only wants to use half a day of PTO?
So I added an Hours requested field

What if the employee is requesting to leave early?
So I added a Leave Early Request field, but I have a issue, I am using a dynamic range in a combobox, but it returns a decimal, instead of 11:00am, if they wanted to leave at that time? Any suggestions, I checked the formatting but I don't see an issue with that...

Also, is there a way, that I can do this cleaner...like Check here if Partial Day and then it opens another tab to choose the hours, if that is "true"?

And finally, I have changed the format to the Calendar, so the dates are Vertical and the names are Horizontal, is it now possible to input the data, in the illustrated format, or one close? Look at the samples, in the PTO Log, and then view how they show up in January's Tab.

Any suggestions will be appreciated, and if I am asking to much, let me know...I don't know what limits excel has?

Hello 00Able,

I assume that the changes made worked to your satisfaction. It will take me some time to review your list and make any needed changes.

Originally Posted by Leith Ross

I assume that the changes made worked to your satisfaction.

Yeah, everything works perfectly, and your close button is much better. I put that code (use the close button) in there orginally, because I thought, if I put the effort into adding the close, I would like it to be used, but didn't know how or if you could get rid of the X...but I found myself hitting the X many time, just to be told...that annoying message...lol

So is it possible to use this format? Or should I change the format to be dates across and Names vertical?

Hi 00Able

As you requested via PM, I've looked at your file PTO Calendar 11.xls; it's changed dramatically from version PTO Calendar ver 1.xls. It appears to me that the Calendar feature no longer works in the new version.

So, please detail the issues you're discovered and I'll be glad to look at them. I could try to debug the procedures but I'm certain you've already done so. Tell me what you found. We'll try to get the basics working then address changes.

John

Originally Posted by jaslake

It appears to me that the Calendar feature no longer works in the new version.

Yeah sorry about that, I just renamed the original textbox "txtDate" to "txtStartDate", when I added "txtEndDate", and did not change that in the original code, I just decided not to fix that, as I knew that could be done, and I wanted to address the items that I didn't know if could be done...

We'll try to get the basics working then address changes

lol how different our approach...

Ok what you see in this Form, pretty much encompasses all my knowledge of the userform, functions, except I can add an option button.

I do not understand what other choices I can use that may perform better for this scenerio, so I created the previously attached userform, to show what I would like, but I do not think it is the best possible use.

So in a perfect world, this is what I would like to happen.

1. I open the user form
2. And it tells me to enter the employee name, I type the name by the dynamic range "NameList" provided (currently in the Combobox).
3. Then it asks me what department the employee works in, again returning a value provided by the dynamic range "PositionList"
4. Then they are asked, "Does the employee request to leave early": Yes/No,
5. If yes then it allows you a drop down to choose a "TimeList" they want to leave early such as 10:00 AM (which is not working properly idk y)
6. Then they are asked "Do you want to use PTO time?" Yes/No
7. If yes then, "How many hours do you want to use?" Again this is returning the a value from the dynamic range "HoursList" in the combobox?
8. And then asks for a Start Date and an End Date by the calendar function, if those dates are consecutive, then information will be displayed in each date in the actual calendar.
9. If the option wants to Leave Early the cell in the calendar will change colors (not the Log), to illustrate that the employee will be there for a partial day.
10. The information provided will be displayed in the Log, and in the actual Calendar

Again, that is what I would like to happen, but I would like a reality check, can this happen? If so where should I begin, I thought you could help as in a previous post you illustrated that it was possible to get multiple cells to be placed in one cell concatenately and thought this might be possible in my calendar.

Hi 00Able



Regarding this

We'll try to get the basics working then address changes... lol how different our approach

I find moving targets terribly difficult to hit. A couple of questions:

• How many employees we talkin' about?
• Does the employee always work in the same department or do they float?
• You said "the UserForm tells/asks me". Do you mean that literally or is choosing from a dropdown adequate?
• Your month worksheets have the employees listed across the top but your example data wasn't displayed that way. How do you want it displayed, each employee under their respective heading?

The challenges are getting the information from the UserForm to the proper month worksheet and then finding where to put the entries on that worksheet. I know how to find the month worksheet.
Address the above and that'll give me enough information to start mulling over an approach.

John

The good thing here is I am going to learn, agreeing with you, "it is harder to hit a moving target"

• How many employees?
  There are probably 500 employees give or take, but only 2 from a department can be off any given day. That is why it is nice to know what department the employee works in.
  
• Do they Float?
  These employees for the most part are in the same department, but they could bid out or be transfered to another area, perhaps, I can leave this field out of the userform, as I could pull it out of another area of the reference "LookUpLists" that actually tells what department each employee is in. But if I did that, then I suppose we would have to make the actual Calendar pull the data from the PTO Log as opposed to directly from the UserForm. So if an employee from Recieving in January requests to use PTO for the week of July, but in May transfers to a different Department, it will automatically update the calendar to show the Current Department as opposed to the one that was originally submitted.
  
• Do I literally mean "the UserForm asks me", or will a Dropdown adequate?
  I do not fully understand the limits of excel, I am always looking for the most userfriendly from an entry standpoint, and efficient standpoint option. I don't want the user to say "No", to a question, but then be given the opportunity to answer opposing what they previously said. For example, Lets say I create an Option in the userform, saying "Does the employee wish to leave early?" They input "No", but then my next field says "What time is employee leaving?" and they choose from the drop down, their regular quitting time, or their regular start time, as they might think they have to put something in that field when they don't. I hope this makes sense. I would rather them not be able to see that option if they said "No". But to be honest anything will be an improvement over last years method.
  
• Ok your last question kind of confused me, but after looking at it, I think I understand what your saying.
  If you look at the PTO log, the first employee that requested PTO was B Smith from the Loading Department, he requested to leave early at 10:00 AM and wants 4 hours of PTO on 1/312011. DBrown was the next employee to request PTO for the same day but he wanted off the entire week of 1/3/2011. Followed by PHarkle who just wants PTO on 1/4/2011. So on the January Calendar, Employee1 is just the first person that requested PTO, regardless of anything else. So if more people put in for it, they will go in ranking order from left to right. We are only allowed to have 12 employees off on a given day, so it stops at 12. That is why DBrown is under the Column heading "employee 2" on Jan 3, but under column heading "employee 1" on Jan 4-7. He was the second employee to put in for the 1/3/2011 but the first to put in for PTO on the other days.

I hope this helps clarify it better.

Hi 00Able

I hope this helps clarify it better.

Maybe, Perhaps, Could be, Don't know. Do I sound confused? Well, only in the sense that I don't know how you're going to use this information.

I spent the last 25 years of my 50 year career (which ended by retirement over 10 years ago) developing systems that made my life and those that worked for me easier. The benefit I had in being in the environment was that I understood what the user was trying to accomplish. I don't have that benefit in working on this forum...so, I ask questions. Sometimes the answers clear things up...sometimes they don't.

I'm going to proceed based on my perception of what you're asking for. I'll post workbooks as I reach milestones that I believe need you're input. I should think you'll see the first post perhaps tomorrow (depending on how the Browns are playing).

John

Well an Ohio team is assured a Victory tomorrow...

Ok, so a little bit more food for thought, I want leads to be able to input this information into the Log, sometimes, these forms take a while to reach the office, and there have been times, that too many people put in for the same day, I have all forms in my hand, but I don't know who actually put in for the date first. (especially around Holidays). I would like somewhere where the leads have the responsibility of inputting the information in, and then I can choose whether to approve or deny, of course I do not want to deny any PTO request, sometimes there are situations when it does arise. We can't run the company without a Forklift operator on staff. At that point if I need to, I can delete the "Log" and it will erase there name automatically out of the Calendar.

Hi 00Able

This doesn't give me comfort

an Ohio team is assured a Victory tomorrow

and we'll deal with this when we have the basic procedures running to your satisfaction

Ok, so a little bit more food for thought, I want leads to be able to input this information into the Log, sometimes, these forms take a while to reach the office, and there have been times, that too many people put in for the same day, I have all forms in my hand, but I don't know who actually put in for the date first. (especially around Holidays). I would like somewhere where the leads have the responsibility of inputting the information in, and then I can choose whether to approve or deny, of course I do not want to deny any PTO request, sometimes there are situations when it does arise. We can't run the company without a Forklift operator on staff. At that point if I need to, I can delete the "Log" and it will erase there name automatically out of the Calendar

You'll have my first go at this tomorrow (unless the Browns are REALLY killing)

John

Hi 00Able

I don't usually do this but decided to post this tonight. I'll let you play with it and sleep on it. This is my first pass and there are a lot of traps in it.

Play with it. I'll work on it tomorrow. Get back to me with what you see.

John

Hi 00Able

Attached version accommodates multiple dates.

John

OK, I made some changes to the userform, after reviewing your post last night. I updated it and attached it right before I left this morning, I am not sure why that didn't post, but not much I can do about that now.

Hi 00Able

I'll wait to hear from you.

John

I have reviewed your recent post, and made the changes to your recent attachment.
Here you will see that I

Took the "Department" field out of the userform, as I can pull this information out of the LookUpList, and I have updated the Log to automatically perform this task.
Moved the Department Column (Previously B to G) in the UserLog
I have included an "Option" button to the useform, that asks the User if this is an Early Request
Changed the name of the comboboxes to be consistent with the information being asked, cboTime and cboHours, I believe I have made all the necessary changes to your code to reflect this.

Issues that I see now,
If you want to choose a time after 9:45AM, it returns a decimal, I have no idea how you fixed that in the first place, what is wrong with it?
In the userform, when using the Option Button, to say the employee wants to leave early, if you accidentally select it, you can not unselect it, can this be changed?
I would rather the Calendar update based upon what is inputted in the PTO Log, that way if I delete a Row from the PTO Log, it would immediately delete from the Calendar", If I need to add another Command Button "Update Calendar" in the PTO Log, to perform this task, that is fine
crossing data between Months employees (as shown employee requests week starting Jan 31 ending Feb 4, it displays in Jan Calendar, but not in Feb.
Everything really looks like it is coming together though...except the Browns Defense... I still rooting for them though..

Hi 00Able

Regarding this

If you want to choose a time after 9:45AM, it returns a decimal, I have no idea how you fixed that in the first place, what is wrong with it?

look at the formatting on LookUpLists, Column C, Rows 2 - 9. I fixed these few rows but not all...that's a lot of typing! Change the formatting on the remaining rows and then type in eg: "10:00 AM" (without the quotes).

I'll look at the other issues and get back to you.

John

Ok, I understand now, you converted it to text, I can fix that.

Hi 00Able

Regarding this

In the userform, when using the Option Button, to say the employee wants to leave early, if you accidentally select it, you can not unselect it, can this be changed?

Use a Checkbox instead of the OptionButton

John

Hi 00Able

If you weren't doin' this

I still rooting for them though..

I'd dump you like a hot potato

John

Use a Checkbox instead of the OptionButton

This is gonna sound dumb, but I am going to say it anyway...I played around with a check box once and I didnt get it.

It must be hard being a Brown fan, I hope at least you compensate the lack of winning seasons, by being a Buckeye fan?

Hi 00Able

Always a Buckeye fan. Don't know which is harder...Buckeye's so close yet so far away or the Browns so far away and not even close. Ah well, as I told my children via email just a few moments ago "Wait'll next year".

If you'd like, I'll play around with the checkbox. The good thing about it is, you can check and uncheck it (I think).

John

Yeah, I think you can check uncheck and a third mixed node, and I think that is where I was getting confused. I was trying to use one checkbox to represent 3 items, and I think I should have used 2, as I think they return a "True False" or a "Yes No", answer. And I was trying to get it to do a "True, False, or Other", but I couldn't get it to work, so I ended up going a different direction.

Hi 00Able

I have your file with the Checkbox if you'd like to see it. If you prefer to develop it on your own, feel free. Let me know.

John

Of course I would love to see, it. I am sure it is probably better than the Option field.

Hi 00Able

It's attached. I've left the OptionButton in the UserForm. It can be deleted.

John

The check box is a much better option, and it appears to work great

Hi 00Able

OK then. As I see it we have two issues(at the moment) to deal with. Both of these issues I've thought about (the traps I referred to previously) but have not yet figured out how to deal with:

• I would rather the Calendar update based upon what is inputted in the PTO Log, that way if I delete a Row from the PTO Log, it would immediately delete from the Calendar", If I need to add another Command Button "Update Calendar" in the PTO Log, to perform this task, that is fine
• Crossing data between Months employees (as shown employee requests week starting Jan 31 ending Feb 4, it displays in Jan Calendar, but not in Feb.

I need to think about these issues. Don't really know how to address at the moment. It'll take a little time to consider.

John

OK then. As I see it we have two issues (the traps I referred to previously)

Transferring from the log should be easy...lol speaking the Blind Man, create another module and attach it to the PTO log to update the Calendar.

The other issue, I am clueless. But I am honest.

Ok time to cheer on the Patriots, not that I like them either...

Hi 00Able

Gotta' find a "hook" said the Blind Man. We'll find it, or not.

John

Hi 00Able

Still working on it. Have the Month End overlap almost resolved. Still have "a little hitch in the rail".

Haven't started on this

create another module and attach it to the PTO log to update the Calendar

I'm assuming that won't be terribly difficult but don't know yet.

John

Hi 00Able

The attached version (I believe) deals with this issue

the Month End overlap

I haven't looked at this yet but will

create another module and attach it to the PTO log to update the Calendar

You will need a reference set to "atpvbaen.xls". It's on your install disk. See this link for more information http://www.cpearson.com/excel/ATP.htm

Need your feedback. Are we on the right track?
John

Originally Posted by jaslake

You will need a reference set to "atpvbaen.xls". It's on your install disk.

Looking into this..will get back with you

Hi 00Able

The attached version deals with this issue

create another module and attach it to the PTO log to update the Calendar

Let me know what I can do to help with this

You will need a reference set to "atpvbaen.xls".

It's part of the Analysis ToolPac and, in this instance, allows the procedure to do some Date Math calculations that aren't otherwise available.

John

Ok I added the atpvbaen.xls in VB, when I run the program I get an error, I know previously you said pictures aren't much assistance... I am hoping in this case it helps.

Hi 00Able

Actually that's the command that the Reference allows to run. My References Screen looks like the attached. What's yours look like?

John

Looks the same, I snipped it right beside yours...

Hi 00Able

Don't know. You appear to be running Excel 2010 when the Error occurs. I've run the procedures in both Excel 2000 and Excel 2007 with the Reference set as described. They both perform as expected. When I remove the Reference, I get the same error message that you get.

I see that you have Excel 2003 and 2007. Do the procedures run in those environments?

John

I am not sure, I will have to test this at work tomorrow and then follow up with you.

Hi 00Able

I may have some additional insight into the EOMonth function. It appears that it became a WorksheetFunction in Excel 2007 whereas, prior to Excel 2007, it was in the Analysis ToolPac. I need to play with this some more today to see what impact this has.

I'll get back to you. In the meantime, if you will, let me know the results of your tests.

John

I played with it at work with 2003 and enabled the Analysis ToolPac atpvbaen add in
It was unsucessful, it returned an error immediately "Unable to find Object", unforturnately I didn't have time to try to get any more information then that. But I did check the "References" to make sure the atpvbaen was active.

I will not be using the 2007 excel, as again it is only on my little used laptop...speaking of which I need a vacation...lol

Hi 00Able

I'm working on a solution that eliminates the need for the Reference. I'll get back to you, hopefully today.

John

Hi 00Able

The attached file performs as expected on my platform in Excel Versions 2000 and 2007. The need for the reference to atpvbaen.xls has been eliminated.

Let me know how it works for you (or not).

John

John, You truely are brilliant. It works on the 2010 platform, I will test today on the 2003 and see what happens.

Works great, Merry Christmas!

Good. Merry Christmas.

John

Hi Kimberly

As you see, this thread has been marked as "Solved"; I only got notice of your post as I'm still subscribed to this thread. To get broad feedback from users of this Forum, you'd be best served starting a new thread with your specific issues. Attaching a file that demonstrates your issue is extremely helpful.

After you've done so, if you wish to PM me of that, I'll be happy to look at your issue with you. I can only guess what's going on with your workbook until I see it.

John

Its funny she should mention that, I fixed the time, but I didn't know how to fix how the date on the calendar always opens in 2012, any insight?

Hi 00Able

Yes, I believe I know what's causing this

the date on the calendar always opens in 2012

It's in these lines of code

Please Login or Register  to view this content.

Change to

Please Login or Register  to view this content.

I changed these lines in my testing because I wanted to work between 2010 and 2011. Forgot to change them back.

See if this fixes the issue.

John

Yeppers, that worked.

John,

Was wondering if you could relook at the code for this calendar, I am having issues with February. It accepts the name on the log, but the calendar does not work from like Feb.10 - Feb 28th. I was having issues with having 2 people off on the same date as well. Not sure why, seems like January and March are working fine.

Thanks

Hi 00Able

The only file I have to work with is the last file I posted on your thread (Post #44)...is it still valid...any changes?

The only change is the year default, but using that one enter 3 people from Jan 1, 2011, to 3 30, 2011 and then look at the calendar.

Hi 00Able

It's going to take a bit to refresh my memory to see what the code is doing. I'm going to be traveling this weekend (until Monday) so it may be next week.

When you say enter 3 people, do you mean 3 that are off for the entire time frame you mentioned (3 months)?

I'll look into it.

Hi 00Able

I've reviewed the code and there are two issues working here.

1st Issue
There are some "white" cells in the February Calendar that appear to have no color but they do.

This is what a cell looks like that has no color
Format Cells Screen Capture Without Color.jpg

This is what a cell looks like that HAS color
Format Cells Screen Capture With Color.jpg

Notice the change in the "No Color" box. Make certain all non-weekend cells in Columns C through N look are formated like the "No Color" screen shot else the cells won't be populated.

2nd Issue
I tested the file overlapping month ends...I didn't test for overlapping multiple months...my error. To fix this issue, you need to add one line of code to two procedures.

In Module 2, Update_Calendar Procedure, toward the bottom, you need to add this line of code

Please Login or Register  to view this content.

In Module 3, Remove_Calendar_Items Procedure, toward the bottom, you need to add this line of code

Please Login or Register  to view this content.

I've attached a workbook with these changes incorporated and all appears to work as expected. Let me know of issues.

Only thing I might add, is there a code, that once you enter a new date in the log, and hit submit, instead of it ending on tht calendar date, could it return you to cell A1 in that worksheet or back to the Log?

In frmPTODelete, add lines

Please Login or Register  to view this content.

In frmPTOLog, add lines

Please Login or Register  to view this content.

A few issues I notice:

1. After inputting the above, code I experience issues with the userform closing, after selecting a date on the calendar, without entering the data. I am not sure if this is an isolated issue or not.
   
2. Also...try entering 3 people having off from 3/1 - 12/30, and then delete those records. Some records will not delete

.

Hi 00Able

I tested the code I provided on the last file I posted and didn't have issues.

Please post YOUR file with YOUR code so I can see what's happening. Please describe what's happening (or NOT happening) in your post.

If we can't get this resolved in the next day or two, I'm going to ask you to start a new thread. This one has been marked as "SOLVED"...other Forum Contributors aren't looking at it so you're not getting their input.

As you suggested I have started 2 additional threads and included the workbook. I really appreciate all of your help.

Welcome to the forum,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.