Autopopulate (horizontally) cells in calendar via UserForm

Hi,

I have a file with some sheets, one sheet for every month-the days from 1-31 are populate via UserForm, one sheet a sort of control/summary, a sheet for public holidays.
When a guy wants a vacation I wanna type the date in a textbox and the required days in another one.
I want to populate horizontally the cells when the values are entered in textbox (the date) and the rq days excluding the weekends and the public hol.
I tried different methods but unsuccessful.
also posted here
http://www.thecodecage.com/forumz/forumdisplay.php?f=64

I really appreciate your help!

Hi John

This formula calculates the 20th workday after the date in A1m Easily adaptable to meet your requirement.

Formula:

Please Login or Register  to view this content.

Hi John

This formula calculates the 20th workday after the date in A1m Easily adaptable to meet your requirement.

Formula:

Please Login or Register  to view this content.

Hi mehmetcik,
Thanks for your solution, I do not want to count the days (yet), let's say a guy wants to take a vacation starting from 10apr for 10days. there is weekend and public holiday, is possible to put a "C" for each day writing the date in textbox and the days only?

I looked at your userform and workbook found them to be overcomplicated.

That is why I gave you the formula.

Try this

If the Textbox1 contains your Start Date and Textbox 2 = your Number of days Holiday then

Clicking on Command Button 1 will populate your worksheets.

Please Login or Register  to view this content.

Hi mehmetcik,

I think I am or you, not sure which one, or both lol, so we are on the right way! Thank you very much for your solution!
I tried it and whatever the month is chosen (I tried with APR or DEC), it populates the cells in JAN without taking into account the weekends....

I ignored the weekends in my first Pass of the code.

I think that the next stage would be to replace the lines that fill the worksheets with a C with a subroutine.

Such as:-

Please Login or Register  to view this content.

So your code becomes:-

Please Login or Register  to view this content.

Hi,

Please Login or Register  to view this content.

soryy can not see why the fillcode is in red...

My Mistake

I wrote the code from memory.

Try

Please Login or Register  to view this content.

Thank you!
it seems it takes into account the weekends, but it populate Jan month only! ...sometimes it jumps to Dec month!

Hi John

I have stripped out all the code in your original spreadsheet.

I have also removed your userform.

So we can concentrate on the area of code that we are interested in.

This seems to work now.

Clicking on the button in Jan will open up a simple useform, Preloaded with 20/12/2016 and 20

Clicking on the button on the Userform will select sheet DEC and will fill ehe workdays from 20/12 to 31/12 with a C

It will then Select sheet Jan and continue.


Change the 20 to 40 and run the macro, the end date will now be 22nd Feb

NB: once we have tested it removing .select will stop the macro switching sheets and make the macro faster and smoother.

Hi,
excellent! the way is really good!
but I noticed that even if we have 20 days, it fills more than 20 working days, it exclude the weekends but not the public holidays.
I tried to skip'em taking into account the cell's color (using offset) but it is difficult with 2 colors (weekends and pbl hol)

Hi George

I see how you want to deal with Public Holidays.

Try changing the new subroutine to:-

Please Login or Register  to view this content.

Your Public Holidays are listed in the Array Hoiday.

Please Login or Register  to view this content.

These relate to:-
26/12/2016 27/12/2016 02/01/2017 03/01/2017


Your Main code needs some other chages to:-

Please Login or Register  to view this content.

I think that the final change is to change how you name your sheets:

ie Dec should be Dec:2016 followed by Jan:2017

That will need the code to be modified but that should be quite easy.


Oh, one point.

I think you should put this formula in Cell AU12 on sheet JAN-2017 and fill it down

Hi,

Thank you! Better 'n better!!!
the month is chosen correctly, it goes to the next month if it requires but it doesn't exclude de hol. I created the array for hol I tried with range but the result was the same, ignored'em!
I attach 2 pics, dec (see holidays 25 and 26dec) and sep (I wanted 5 days and I received 6 days lol)

dec.PNG
sep.PNG

Thx again!

Hi nigelog,
Thank you for your solution, I tried one before but I want to have in the same file/sheet the roster!
I know, it's a complicated file, actually it's a huge file, the vacation part is a small part of it! I hope the guys from this forum can help me to solve this issue....in order to have "all in one"!
Thx again!