Close Topic Options

Hello,
I had seriously hoped that I could avoid seeking assistance, as I have always been one to find my own answers, and tweak what was necessary to accomplish my goals. I have been working on a Spreadsheet for quite some time before I literally decided to take a plunge into the world of VB. Details are as follows...


Project Description

An employee data base that maintains data on several excel spreadsheets. This data will be edit-able & viewable from a Userform only (more idiot proof). The WorkSheets are fairly simply, consisting of...

- DATA - Currently No Marco's or formulas
(A.) LAST NAME
(B.) FIRST NAME
(C.) DEPARTMENT
(D.) SECTION
(E.) PRIMARY
(F.) SECONDARY
(G.) HIRE DATE
(H.) PAY CODE

- VACATION - Currently No Marco's or formulas
NOTE: Will only maintain names of employee's in which have submitted approved requests.
(A.) LAST NAME
(B.) FIRST NAME
(C.) START DATE
(D.) END DATE
(E.) HOURS - Calculate the Number of days multipled by either 4 or 8 (Not Yet Added)
(F.) COMMENTS

- PERSONAL - Currently No Marco's or formulas
NOTE: Will only maintain names of employee's in which have submitted approved requests.
(A.) LAST NAME
(B.) FIRST NAME
(C.) START DATE
(D.) END DATE
(E.) HOURS - Calculate the Number of days multipled by either 4 or 8 (Not Yet Added)
(F.) COMMENTS

- WORKED -
NOTE: This worksheet has not yet been started
LAST NAME
FIRST NAME
DATE
HOURS WORKED
COMMENTS

USERFORM:
Is a single userform in which displays the DATA worksheet information and the Active X Calender Control. This UserForm also consists of two separate Multi-Page Controls, in which displays (1) Request (Vacation and Personal) Break Down, overall hours for the entire year. (2) Individual View of each request for that specific employee in which is stated in the DATA portion of the UserForm.

Request (Vacation and Personal) Break Down -
Has several Multi-pages as well, for each worksheet except the DATA sheet. This has several Text Boxes, Labels, and Option Boxes. Text Boxes represent each month, for TOTAL HOURS and TOTAL REQUESTS.

Individual View of each request well also consist of other Multi-page views. One in which will display all Requests for that Month. One for Each Day, and Another one for just the employee.

PROBLEM
I want the jan_tx to sum total hours of the employee in which name is displayed in the last_name_cb. Data will need to be extracted from the VACATION worksheet. But also ignoring all dates that do not fall into the JANUARY time frame.

I have tried using the INDEX MATCH, FIND METHOD, and the VLOOKUP, all with no luck. I can not seem to get any method to read any of the controls...or when I get it to read a control...I can't seem to get it to produce the result in another control.


I hope this post is detailed enough into explaining both my problem, and my goals. Please feel free to post any question you deem necessary.

Any and all assistance would be greatly appreciated.
Thank you,
Ghost

Hi Ghost, welcome to Hardcore XL VBA.

This all seems pretty straight-forward, but instead of a description it would be best to upload your workbook so we can see exactly what it is you want and also so we can test any proposed solution without having to build a copy ourselves. i.e. we really need to see the layout, format, control names, etc... Just put some dummy info in and zip the file then use the 'File Attachments' button to upload it.

John

FILE ATTACHED

Thank you johnske,
I tried to supply it earlier in my post, however cookies was preventing me from doing so. Mainly due to my first attempt was while it was still a .xls file. Either way, here you go.


I understand what you mean by needing the file, as I am a webmaster utilizing Nuke-Evolution. I provide support and porting needs for other users...often needing the file in which they are having problems with.


Thank you,
Ghost

Attached File ( Number of downloads: 59 )
 Employee_Managment.zip

Ok Ghost, some points…

1) Your userform is just too large, on my PC it goes off the screen. I don't know whether you intend to distribute this to others, but the size of the form will vary from PC to PC depending on each PC's screen resolution, so it's best to assume others will be using a smaller resolution and build it accordingly. I've resized everything to a better size but you'll need to tidy up and maybe re-arrange it

2) You'd be better off using Select Case statements rather than lots of ElseIf's - it's faster. (Have a look at the attachment)

3) Rather than stipulating properties in the property box of each control, you're better off putting those properties in the main body of code - it's then much easier to find and change things. e.g. the row source property is best put in the code as I've shown.

4) The way you'd named your controls (with underscores and abbreviations such as cb for combo box) makes it really hard to read and follow the code. There's nothing to stop you doing it that way of course, but you need to consider that one day someone else may be called in to modify your code and so it's best to not veer too far away from conventional naming rules, and use abbreviations that are more intuitive for others - oh, and use them consistently. I've renamed almost everything - have a look...

5) I'm not sure exactly what you want here >> "I want the jan_tx" ... (now JanTxtBox) ... "to sum total hours of the employee in which name is displayed in the last_name_cb. Data will need to be extracted from the VACATION worksheet. But also ignoring all dates that do not fall into the JANUARY time frame."

You only have one lot of total hours in your dummy sheet - will there be other entries on other rows to sum? Also, will all the entries on this sheet (and the others) always be sorted as (per the demo) and the same names as the DATA sheet? - you'll need a 'sort' in a procedure if so

I've assumed the latter is to be so and just pasted links to the DATA sheet. However this means there's a lot of duplication - have you thought about having all this on the one worksheet? (It also makes it a lot easier) I've put it like that on a new sheet [DATA(2)] as another idea for you to consider...

There are several ways to get the sums of cell contents via VBA so I'll just leave this bit till I get some more info from you.

Regards,
John

Attached File ( Number of downloads: 56 )
 Employee_Managment_V1Mod.zip

Thank you John...
And here I felt I had reason to be proud of myself...less than two weeks ago, I had absolutely no knowledge or understanding of VB. This project was nearlt completed..but was only a excel document with ALOT of formulas. It got real big and slow real quick. I have restarted this project at least 4 times already mainly due to not having the file with me, when an idea occurs.

As for the problem in which I came seeking your assistance...
There was no intention of sorting this...I can not fanthom a purpose.
The employees can and will likely be entered multiple times - as the current policy at work now. So diffenately duplicates.

I was originally thinking of putting everything on to a single worksheet, however...when it came to one employee submitting 14 individual vacation day requests (PANS)...I could not envision how to get the data to add to the sheet affectively.

I will go over your suggested changes today at work, along with changing the necessary additions I added yesterday. As for the addition of property controls into the VB code...that never crossed my mind...so certainly something I will take into consideration as well.

Also the screen size...'lol', I had that same issue yesterday when I sent the same file to my work PC. Opps!!! Not necessarily easy...the userform when resizing...wanted to literally jump to where it was acceptable, before I was able to alter the other controls...causing them to disappear.


Thank you John...looking forward to your suggestions.

Ghost

OK, some more for you to think about before you go too much further...

This could all be done on a separate worksheet (you can have combo boxes/list boxes/drop down lists/option buttons/calendars etc on a sheet) and I feel this would be a better way to go. You can think about this worksheet as your main 'control panel' and the other sheets as your data sheets.

However, previously you mentioned making it idiotproof, by which I assume that the issues of formulas on a worksheet that can be easily overwritten were in your mind. But this can be prevented in several ways i) you can protect the cells that contain formulas, or ii) you can set the scroll area so you can't even select the cells containing formulas.

The most efficient way to go is usually a combination of worksheet formulas and VBA code. Now, as you're new to VBA, you probably don't realize that you can use event code in the workbook or on a worksheet? This means that you can (say) select a name from a combo box, the name will be entered in a given cell on the sheet and this worksheet change (which is just one of several types of 'event' that can be used) can then fill in the other cells just like you've done on the userform.

If you want to see how this sort of thing works, let me know and I'll do up a quick demo for you

Cheers,
John

Ok johnske...
I have been thinking long and hard regarding putting this all onto a single work sheet, however I am failing to see how that is even possible. And here is why...

VACATION WORKSHEET:
Will tally every Vacation Request. A few requests will be for one week or more...but most range from a single day, to three days. If every employee gets a maximum of 10(80 Hours) days a year, with a company policy of 'Use it or Lose it' this adds up to many requests. To make matters worse, every employee can utilize their vacations in increments of 4 hours. This is Paid Time Off.

PERSONAL WORKSHEET:
Much like the VACATION WORKSHEET, however...employees can use the time in increments of 2 hours. This is Unpaid Time Off.

WORKED WORKSHEET:
This worksheet will actually play a big role in future additions to this program. As of currently, it will only be used so that Supervisors may track approved overtime, as often times employees are hanging around and clocking out later for the additional overtime pay. Supervisors will use this in conjunction when approving time sheets, as well as keep track of those that continuously break the rules. In the future, this will also be utilized along with production numbers to evaluate an employee's effectiveness. The purpose of this worksheet is to track daily hours of each employee, it has not been decided if the tracking will only be of overtime...or all hours. As their is also the concern of absences - without the fear of infringing on employee's privacy policy.

As for this project, it is far from a complete project, and will have many other functionalities installed. However, most of it being strictly Excel Worksheets. With a few formulas and even macro's.

The purpose for the UserForm is as for the purpose of 'idiot proof' however, it was not the sake of protecting cells or formulas, as I am very aware of how to secure that side of excel. The 'idiot proof' remark was in reference that those who will be granted access to this, are not computer literate people.

When I originally started this project...its intentions were to remain excel based. But then I started running into issues of MANY Hyperlinks and Formulas that literally bogged the system down, in load time, response time, and file size. I have this Userform in near mimic state without all of those inconveniences. Of course that is not to say I was doing something wrong...'lol'. However I do feel confident in my knowledge and capability in Excel alone.

I opened your supplied file and I simply was impressed with how you had nearly sliced my coding in half. Instead of me simply using you file to continue my task...what I did was studied what you had done differently (I learn best this way)...and redone my work. This included resizing the userform itself. I have also added in the additions I had performed the night before, when after I posted here seeking help.

WHAT YOU WILL FIND DIFFERENT:
I never could never figure out the whole thing about CASE SELECT, until I seen it in with your file, this was due to understanding the entire layout. Often times, examples never go indept about the layout. Eitherway...you had a select case for the Year.

You will find that I did not include this only because I could not figure how to get it to work with the changes I made. Prior, the Year was determined via a Text value in the code. Now I have altered the code to utilize the 'Todays Date', this way the Program can cross into year to year, without additional editing just to change the year.

I have also utilized highlighting to alter the colors of text boxes for better recognizing ones actions. Although not necessary, it helps alert the user 'where' he is.

Although I have renamed most of my controls, in the same manner as you suggested, they were not done precisely as you had, as flipping from file to file got entirely to time consuming.

AND NEXT???
Well I made another attempt into getting the SUMIF to work for adding the VACATION HOURS for each month. Learned about the DSUM and even gave a shot at the Conditional SUM (BAD IDEA). During this...it came to my understanding that this task was not going to be easy, and in which I was likely going to make some changes about how I can get this information.

Conditions that needs to be meet to sum total hours:
Employee Last Name
Employee First Name (In Case, mutiple employee's with the same last name)
Start Date and End Date has to be within the range of the month in which the box represents.
If an employee takes vacation near the end of the month, that extends through the next month, can we calculate this to sort out the days/hours in which do not fall into the corresponding box? And of course take those days/hours and add them to the next month?

Tonight my plan was to simply calculate this in the worksheet itself, and link the control to the column to pull the information. When I came across another concern. HOURS. Due to the optional hour groups that employees can use when taking Vacation or Personal Time...this could cause some conflict and misunderstanding (work related conflicts). So I thought it was best suited that the HOURS column represents the Hour Group in which the employee is taking (2,4,6,8) and that I use the DATEIF to calculate the number of days. Multiplying these two together will give me the total hours. On the VACATION WORKSHEET, you will see the slight change I have made to reflect this...none of the other Worksheets have been been updated.

I have also added more columns to the DATA WORKSHEET, intending to use for collecting the TOTAL HOURS on VACATIONS, PERSONAL, and WORKED for each month.

It is amazing what you have done...I thought what I had going was right on target...but you managed to shorten up the coding quite a bit. I know there is still alot I need to learn let alone understand regarding the limitations, security, and of course compatibility with everyone else.

I have supplied the newest file...feel free to look it over...
criticism's is certainly welcomed, suggestions are definitely encouraged, and guidance is positively taken.

Thank you so much for what you done so far...looking forward to much more.

Ghost

Attached File ( Number of downloads: 46 )
 Employee_Managment.zip

Hi Ghost,

Sorry, I haven't had enough spare time to look at this properly yet, and the main thing I've been looking at is that the calendar pops to a new position when you change pages on the multipage.

I don't use multipage myself, but I think the problem arises because you've inserted a calendar object into the multipage - this is unusual.

Another problem is that I've noticed is that calendar 9.0 doesn't always transfer from one office version to another. For instance, your calendar does show on my machine - but without any dates, they're all blank.

Ways around these problems?
1) Can you perhaps reduce the size of the multipage so the calendar can be put on the userform itself? - get back to me about this...
2) Different versions can be catered for by using code to create the calendar when it's loaded on another machine, however this is not as straight-forward as it could be - it involves checking the hard-drive ID number to see if it's a different machine, then setting a GUID reference before creating the calendar - and the VBA project cannot be locked. In addition, from office 2002 or 2003 up MS placed a security warning for when code that writes to the VBE window is run. This can cause great dramas for some ppl, so you need another procedure that's less stressful and more helpful to users to help allay their fears and guide them through this process.

Another point is that I see you're using RGB to set the back colour. You only use RGB to create new colours that aren't in the workbooks colour pallete. For standard colours you can use...

Object.BackColor = vbBlack
Object.BackColor = vbRed
Object.BackColor = vbGreen
Object.BackColor = vbYellow
Object.BackColor = vbBlue
Object.BackColor = vbMagenta
Object.BackColor = vbCyan
Object.BackColor = vbWhite

(and there's no need to set default colours)

Hi John,
In all honesty, I would rather not use the Multipage, cause it is simply more cluttered in my view. However, the Supervisors that want this...has suggested that it makes it all the more easier for them. As for the calendar...I also want to move it off of the multipage as there is simply no reason for the Userform to have 3 calendars. Yet, I am at a loss as to how I can move this off...shink the multipage and still display all of the pertaining information per multipage tab. If you have a better setup suggestion, or layout...please by all means give it a go.

I replaced the RPG with the Object.BackColor that you had suggested without any problems, but when I tried to remove the DefaultColor, it would not turn the fields back to white as I want. I have been searching for solutions, yet the only examples I can find were 'All Control Highlighters' (in which I do not want), and I simply could not determine what part of the code would return the controls to their defaults. I tried adding the DefaultColor scripts to their pertaining Select Case...but that did not do anything either. I have even tried having these lines replace the Call DefaultColor...same result.

In this file, I have accomplished alot in regards to getting the data to calculate just the way I want...many added formulas, fields, and extra test vacation data. Although most of the formulas I hope to remove, as it slows down the worksheet immensely (about 1 min load/calculating time), I left them in currently to show options and examples as to what I am trying to achieve.

In my last post, I had mentioned about the bad mistake of testing the Conditional Sum. Well, I have put the code back in to prove that point, but also so that I can in fact test out the other formulas I have added. Today I learned of another way in which I can have my controls run formulas using the EVALUATE, yet I feel this might not be what I am looking for.

SORTING DATA:
On the VACATION worksheet, I have several additional columns to calculate necessary fields, in which aide in other calculations as well. When taking into consideration of what is needed when tracking employee's vacations...their are many factors and reasons.

1) Ensure that employees VACATION time frame does not interfere with other employees of importance.

2) When calculating hours of vacation taken in the month of January, it becomes challenging when an employee asks for days that carry over to the next month (01/27/07 - 02/05/07). I would need a method of only selecting the January days for the JanVacHoursLabel, and the February days to be added into the FebVacHoursLabel box.

3) Weekends and holidays are never considered vacation days, therefore they will need to be omitted as well, when calculating the taken and remaining vacation hours.

With everything I have added (fields and formulas) I can not do this...and it seems to be accurate. To include the ability of separating the years from one another, to include ensuring that when a vacation request crosses into another year.

Now I have tried to get these numbers to appear in the Monthly 'VacHoursLabel's, with success, somewhat. Yet I believe there is a much smarter way of doing all of this. With my Conditional Sum, I am using the following

And I simply have my label point to that cell.

Then I cam across the EVALUATE

My first thought was "THIS WORKS!!! This is exactly what I want..."
But after some thought I have come to realize that I would have to added this 150 time for each employee times 12 months times another worksheet (PERSONAL). And of course another simalar yet different worksheet in WORKED. OUCH, that is not what I want to do.

So I looked at the code that I have now and noticed how the Combo Box draws its data using

But simply decided that it might be unnecessary coding and that maybe a function was what I needed. So I looked into creating functions, but the tutorials I found were simply to short for me to gain an understanding.

Also regarding the issue of not being able to see the dates...my guess is because of the color I accidentally changed, but always forgot to replace...my home PC also had issues of seeing these dates. The color that is set now...works for me...hopefully for you as well. As for the calendar being in different positions, that is because I moved the calendar during all of these writting and testing...and never tried to realign it. I have removed all of the controls from the other screens except the Stats page. So this should no longer be a problem. Before I moved the calendar...everything changed from page to page without jumping from position to position.

Let me know what you think, what can be better, and where I am going wrong. Your assistance is greatly appreciated.

Thank you,
Ghost

P.S. Do you know that the Calendar Control is at Version 11.0.5329.6 now? I have the MSCAL.ocx file, I have supplied it with the Employment Manager file...that may also be part of the problem you are having. As for the other PC's at work...none of the computers in my company have this file...so I will need to individually upload this file.


EDIT: I tested that EVALUATE some more...and you know, it might actually work without all of that extra coding. It seems to work across the other employee's without having to individually add them.

Attached File ( Number of downloads: 36 )
 Employee_Managment.zip

OK, we need to sort out the groundwork before proceeding further here...

I'm still only seeing a calendar with no dates on it. The problem is that "Calendar" came out with versions 6, 7, 8, 9, 9.1, 10, and you say it's now 11 as well. I have office 2000 with version 9, never-the-less I've downloaded examples that say they also use version 9 and have experienced the exact same problem.

There's another calendar available called "MonthView" and if you're going to distribute with the ocx file you may as well consider it, I've just added it to your userform in the attachment but you'll need to download, install and register the ocx file from here http://xlvba.3.forumer.com/index.php?showtopic=62 note that I've tried it and it does not jump off the multipage when you change pages.

EDIT: another advantage with Month view is that you can resize the calendar to quite a small size without losing any info being displayed as I've shown

Attached File ( Number of downloads: 65 )
 Employee_Managment_MonthViewCal.zip

BTW, I just had a quick look at the worksheet coding - it was taking forever to recalculate every time the workbook was opened...

This is a good case for the use for event code i.e. a worksheet change event does the calculations only when a cell is changed - and it doesn't recalculate the whole workbook, only the calculations that are relevant to the entry that was made.

That way we can eliminate almost all the worksheet formulas - and no, we don't have a line of code for the code in each cell on every row, we use a (single) dynamic code that's applied to the row where the change is made regardless of what row is actually changed.

Perhaps I should expand a bit on my previous post...

There are basically (usually) 2 fundamental ways to do the same calculations

1) with worksheet code/formulas

2) with VBA coding

Both have their advantages, disadvantages, and limitations. As I said previously, you use the advantages of both to obtain the best and fastest solution.

The best solution may involve a straight VBA solution, or, it may perhaps involve using VBA to: insert formulas in cells to do the calculations and then immediately erase the formulas and replace them with the values found from the formulas, and Of course there is also the option to use VBAs "Evaluate", however Evaluate is usually reserved for use with special cases that involve worksheet formulas, so it's best to stay away from it for now.

Your workbook is already taking a long time to calculate and this will get even worse as more entries are made, so now it's really time to consider using VBA and event code to do most of the work.

Event code is just VBA code, except that pre-defined events are used to trigger the code.

You've already discovered a very basic example of an event with your userform code i.e. you click an option button and some other things happen - this is essentially a "Click" event - but there are many other events, such as opening or closing the workbook, activating a sheet, making a selection on a worksheet, or changing something in a cell (these are just for starters) any of these events can be used to trigger VBA code that can make very simple or very wide-ranging changes and/or calculations in the workbook.

Also, I notice you're using the EOMONTH and NETWORKDAYS functions, I don't know if you're aware that these are not standard excel function and they require the analysis toolpack addin to be both installed and available (note that this is not installed by the default office installation) and you never know whether other users have it on their machine - this is a case where we probably should do the calculations with VBA to keep the workbook self-contained and independent i.e. it's best to create our own functions that can travel with the workbook. I've just made up and posted some UDFs (User Defined Functions) that will do this here > http://xlvba.3.forumer.com/index.php?showtopic=330

Did you load the new type of calendar OK? And what do you think? - Oh, and I should have mentioned that you resize it simply by changing the size of the font.

Sorry I have not responded yet, however I was hoping that I could do so with something to add. Unfortunately nothing on a positive note. I do like the new calendar certainly more appealing, I have yet try and move that off of the multipage though. I have seen your latest 'how to' and will implement that, even though all of my computers that will be having access to this computer do have the Analysis Tool Toolpak. I have spent the entire weekend trying to seek a dynamic example in which I could manipulate my code to work with. I believe I found the right one, however nothing that explains how to use it. The OFFSET, I will continue looking into this later today....The EVALUATE turns out to be nothing but a failure for me when adding this to the rest of the textboxes. I certainly made a huge mis-judgement when I stated that this was working...I believe I was probably lookijng at the wrong controls when validating the calculations.

Once I get somewhere with this dynamic code...I will post back here with the changes...

Thank you,
Ghost

OK, here's your workbook with event code for what you have now. This code is triggered when you activate the relevant sheet - I've simply copied your formulas from the cells and made it so that all the cell formulas are written into the cells on the sheet, the cells are calculated and then the formulas are replaced with the values (so you don't have to worry about someone typing in a coded cell and erasing the code ).

This is not the fastest way to do it (it would be better - but more complex - to only apply code to the line on which you've made an entry or change). However you'll get the idea and see that calculation is much much faster now.

The code is in the worksheet modules if you want to view it (in the VBE window, click on the DATA or VACATION sheet shown in the Project Explorer)

Code for DATA sheet:

Code for VACATION sheet:

I've also added a 'helper' column on the VACATION sheet (column X) - you can hide this if you like...

Attached File ( Number of downloads: 28 )
 Employee_Managment_MonthViewCal1_with_events.zip

WOW...this is awesome John...seriously AWESOME...and certainly fast. However...on the DATA sheets, where it shows the calculated hours per month. It shows the same hours for everyone. I looked over the code and realize that the problem resides in the code I had in there in there first place.

=$A3 and =$B3 need to alternate accordingly per line in which the script updates. Today at work I looked into trying the get the OFFSET to only affect these area's, but again no luck. I also had a friend tell me to try out the FOR Statement...but I was getting no where with that. My last attempt was to try removing those lines in which validate the Last Name and First Name...and that only gave me errors.

It is only my best uneducated guess but I think that the For Statement would accomplish what I need, simply cause it is quite similar to what you have regarding the LastRow line.

I went through the rest of the code you added and was simply amazed at the Date functions you added, certainly adds speed and instant updating on a trigger(ACTIVATE).

I will post again once I get this part figured out. Thank you...very much, you have already done more than I anticipated.


Ghost

Well I didn't know what some of the heading abbreviations stood for, and I didn't look to understand what the objectives for each column were, but perhaps if I did, the problem code may (perhaps) be better done as a straight VBA calculation

Right now the code validates that the LAST NAME column(VACATION!A) and FIRST NAME column(VACATION!B) are both the same in the DATA worksheet. If so, then it calculates the total S.Hours in which S.Month = 1(Month Value)

+

E.Hours in which E.Month = 1(Month Value)

Ghost

I'm still unsure about what you're doing here. The copy I have had many rows with the same name but different start dates on the VACATION sheet (??) - I've assumed this would not be the case in the finished book and I've thus changed it to make them all unique names.

I'm assuming that you just want to add S.Hours and E.Hours for the 3 years (2006, 7, and 8) and place them in the relevant holiday month on the data sheet (do you really need all those zeroes there?) here's the revised event code for the DATA sheet based on this assumption (this replaces all the formulas).

Is this getting close to what you want to do?

Attached File ( Number of downloads: 37 )
 Employee_Managment_MonthViewCal1_with_events1.zip

The VACATION worksheet is a log in which ALL VACATION requests will be maintained. It is extremely important in which this log holds duplicate names, as each row represents one request. This way I can tally the total hours of vacation in which last name 1, first name 1 has taken..and so on and so forth. Sometimes we have employee's that will take every Friday off until they have exhausted all of their Vacation hours. This would easily calculate to 4 Fridays a month at the very least. So at 4 separate requests 1 day each for 8 hours each, this would total to 40 hours for a single month.

I know this is beginning to become more of a nuisances, and I would like to apologize. I to often times find myself lost in what I am trying to accomplish.

Thank you,
Ghost

So if there are numerous rows on the vacation sheet for the same person with different start and end dates ... these are the vacation start and end dates are they?

How do you intend to equate these with the data on the data sheet? Will there be numerous rows for the same person there also?

Yes, the Start Date and End Dates reflect the starting and ending of the Vacation. The equation I am trying to perform is...

If
FIRST NAME AND LAST NAME from the VACATION worksheet
matches
FIRST NAME AND LAST NAME from the DATA worksheet

Then SUM all S.Hours and E.Hours in which correspond to the Month and Year Table. Months must match that in which the data is entered into.


This should then calculate the total hours of vacation in which last name 1, first name 1 has taken in January 2006...etc.

TABLE EXPLANATION

START DATE: Official Start Date of VACATION

S-E.Month: Evaluates the End Month date from the START DATE. Necessary for any vacations that run into another month. (01/28/2006 - 02/05/06). This allows me to separate the total hours taken in the month of January, and ignore those days that carry into February.

S.Month: This is the Month Value of START DATE

S.Hours: Total Hours taken during the START DATE month period.

E.Month: This is the Month Value of END DATE

E.Hours: Total Hours taken during the End DATE month period.

S.Days: Total number of days determined by START DATE AND END DATE, unless the END DATE does not equal the same month as START DATE. If they do not equal then the S-E.Month replaces the END DATE query.

E.Days: Total number of days determined by END DATE and E-E.Month, unless the START DATE equals the same month END DATE. If they do equal the same value, nothing is done.

E-E.Month: Evaluates the End Month date from the END DATE. Necessary for any vacations that run into another month. (01/28/2006 - 02/05/06). This allows me to separate the total hours taken in the month of January, and ignore those days that carry into February.

HOURS: Number of hours in which employee is granted for each day. Necessary to multiple with S.Days and E.Days to gain value in the S.Hours and E.Hours columns.

I hope this better explains my attempt...I know the headers are not the brightest in titles...'lol', but I was in a hurry trying to test out my theories. But the overall goal is to determine the total number of hours of taken vacation for each employee per corresponding Month and Year.

As far as numerous rows on the DATA worksheet for the same person, the answer is no....

Thank you,
Ghost

OK...this is what I have done...
I have hand entered the summed data into each necessary field...with a colored formatted comments box in a couple of fields, to explain how the data was obtained.

I used the AutoFilter to break the list down by Last Name, then First name, then Filtered each month I was trying to SUM. From here..I just added the hours in which fell into the same month. Do both month columns separately. I hope this better explains everything...

Thank you,
Ghost

Attached File ( Number of downloads: 28 )
 test.zip

Sorry Ghost,

Have been to busy to do any more than just take a quick look at this. Will get back to you as soon as I get a chance to do something with it.

Regards,
John

So any luck on this?
I have been playing around with the code and in all seriousness...getting no where.

A friend suggested I try to use a pivot table...Is that possible? Could I extract the information from there and have it appear per Employee name in the proper boxes? What are your suggestions?

Any and all assistance would be greatly appreciated.

Ghost