Making a leave tracker with VBA

[Markiepoop]

Hi all, i'm working on a leave tracker at the moment (please take a look at the attached file at the bottom of the post).

Currently, if a staff applies for leave from the 27 Jan 17 to 05 Feb 17, I would manually merge the cells and fill it with a light blue color, inputting the date range and number of days used in this manner: 270117-050217 (5).

I would like to use a userform instead. In the userform, I plan to have four text boxes which are:

1. Date From (in a form of ddmmyy)
2. Date To (in a form of ddmmyy)
3. Name
4. Number of leave used

The macro, ideally, will do the following:

1. From the name inputted, it will go to the row of the person
2. From the Date From and Date To, it will merge the correct cells together and fill it with a light blue color.
3. Lastly, it will insert the data in the form of: ddmmyy-ddmmyy (leave used)

I appreciate all help and direct I can get.

Thanks,
Marcus

18169

[SamT]

merge the correct cells together and fill it with a light blue color.
Many Professional VBA coders refuse to use Merged cells, would you settle for light blue and Horizontal Alignment = "Center Across Selection?"

[Markiepoop]

I think in this case I would be okay with both merged cells or center across solution. Both are fine.

[SamT]

And you want the result to look like. .
240117-30011 (5)
Where the number inside the parens() is the total number of days of leave requested?

Do weekends and Holidays count as leave days? Business Rules apply.

Before we get started, what happens to the Leave balances in columns C and D. Business Rules apply.

I'm assuming that you didn't take a lot of care when you created the attachment because:


The report for Vernon shows 270117-050217 (5), but the blue cells are all in the last week of Jan and include 10 calendar days or 6 workdays.
When is leave earned? You're showing many days available for 2017, but it's only Jan. Business Rules apply.
All the months are broken into sections of 8, 8, 8, and 7 days, even February. This is a real problem because Excel and VBA think that Feb 31 is actually Mar 3rd. (except in Leap Years)


FYI, 53 weeks covers a year and a day, and 57 weeks covers a year and a month.

Would this visual work?
Jan 1 Jan 8 Jan 15 Jan 22 Jan 29 Feb 5 Feb 12 Feb 19 Feb 26 Mar 5

The first cell is a Date, (01 Jan 2017). The rest are the formula =F5+7 filled across, and all are custom formatted to mmm dd, then the row is Copied and Paste Special + Values. That is designed to work with VBA.

I personally would use the date of the first Monday in Jan (Jan 2) as the starting date.

You can design the sheet layout the way you want, or, whoever takes this project on can design it the way they want, just kinda sorta following your attachment.

If you need it broken up by Months, you will have to manually insert the first date of each month and the dates won't correspond to weekdays.

[Markiepoop]

1. That is right. The number of days used will be indicated within the parens()
2. Weekends and holidays do not count. However, it is not important as for now, I plan to manually count the leave days taken (it would be a great addition in the future but i wish to solve what I have on hand at the moment)
3. As with the above, I plan to manually handle the leave balances for columns C and D for now. In the future, i hope it will be able to auto deduct accordingly to the number of days consumed. The column 2016 represents the number of leave days brought over while the column 2017 presents the leave days in the current year.
4. Apologises, indeed, the blue cells ought to cross over to the first week of February. It only indicates 5 working days as the personnel leaves on the evening of 270117. Again, another issue I plan to work on in the future. Keen observation!
5. Leave is earned at the beginning of every year. As I have the dates when the employees resign, I am able to give them their leave days accordingly.
6. Noted about the problem with February. That is something i was unaware of.
7. If possible, I would prefer the current visual, with the exception of perhaps February, which would be altered to 28th/29th instead. Are there any solutions to allow for the maintaining of the current visual?
8. Yup. That is okay.

Thanks for your time and insight.

Regards,
Marcus

[SamT]

Good luck with that.