I need the help and patience of a real expert here

[Special-K]

I've got an excel sheet that tracks vacation. I've put a great deal of work into it with many thanks to the guidance of

countless others on this forum.

Other departments at my work have heard and asked for this handi-work ... and with their requests have also come requests

for more functionality. (What have I done?)

The latest change to my "vacation tracker" is well over my head ... and I wish to put a plea out there to a real expert who

knows VBA with whom I can share my file with and get help with.

I can try to describe my delimma here:

The first column a user enters a code:
These are codes for "whole days" of vacation.
CO (Carry Over from last year)
LV (Lee Vacation)
PD (Personal Days)
ED (Earned Days)

Another set of codes represent "half days" of vacation.
CH (Half Day from Carry Over category)
LH (Half Day from Lee Category)
PH (Half Day from Personal Days category)
EH (Half Day from 'Earned Days' category)

The last set of available codes represent vacation taken "Hourly"
HC (Hourly subtraction from 'Carry Over')
HL (Hourly subtraction from 'Lee Category')
HP (Hourly subtraction from 'Personal Day' category)
HE (Hourly subtraction from 'Earned Days' category)

In the Next Three columns are: Start Date; END Date; and TOTAL
So, after a couple of months, entries might read like this:

CO 6/10/06 6/15/06 6
PD 6/20/06 6/25/06 5
PH 6/29/06 6/29/06 .5

Now ... fast forward down to the bottom of the sheet, the numbers in the FOURTH column will be tallied and subtracted from

within the respective category (based on the code in the first column).

So:
(CO) Carry Over Days allowed: 15 .... Used 6 .... Remaining: 9
(PD) Personal Days allowed: 6 .... Used 5.5 .... Remaining: .5
(LV) Lee Vacation allowed: 20 .... Used 0 .... Remaining 20

I've got everything working perfectly ... except the whole Hourly mess.

What I wish is:
When a person enters any of the hourly codes (HC, HL, HP, HE), a user box prompts the the number of hours to charge ...

then fills it into the fourth column) The user can then manually fill in the dates in the second and third column to

represent when the vacation is taken.

My problem gets stickier as some people are union members and others are not. This means that a workday is varying lengths.

So I've got a VARIABLE box at the bottom of my excel sheet where the number can be changed for each employee. But I would

need that number included in the calculation.


Here's what a couple of columns should look like:
HP 7/1/06 7/1/06 2 Hrs


At the bottom totals row for that category it should read:
PD Allowed: 7 .... Used 5.5 Days 2 Hours .... Remaining 1 Day 5.5 Hours

(The HP and PH categories are also subtracted from same category as (PD Personal Days)



Oh, it's confusing. Is there somebody who can lift me out of the hole I've dug? I would be in your debt.

Thank you
Kevin.

 

[acw]

Kevin

The following sheet event should bring up an input box whenever the first character entered into a cell in column A is "H". It will put the output into column D.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count = 1 And Target.Column = 1 And UCase(Left(Target, 1)) = "H" Then
    Target.Offset(0, 3).Value = Application.InputBox(prompt:="Enter number of hours", Type:=1)
  End If
End Sub

Can't fathom enough of the rest to try to work out the formulas for the calculation tho...

Tony

 

[Special-K]

Big help

That's fantastic! Thank you for guiding me in the right direction.

Cheers to you my friend.

Kevin