on my staff attendance sheet that I did a previous video on and I have put a
link on on this video on this video to the previous one but I have designed a
form for you to enter the leave for any time during the year of by
entering it on a user form so I'll show you how it's done
and so I click on enter leave user form comes up and your employees are listed
here and you basically just enter the employee that you'd like to use and so
I'm gonna click in employee and I'll select for you select from the months
and I'm gonna select February and they would like february 5th through the 9th
and now I'm going to click enter leave now so we're gonna clear the form and
we're gonna close it and then we are going to scroll across until we get to
February and there you see that the leave is already entered in here now you
could enter for any you wouldn't have to scroll for each month to try to find
which days they were leaving you could just put it in on the user form so let
me show you how I did it so I have my user form the name ok for the month it's
a combo box and how I entered the so let's click on the combo box and if you
go down to row source down here it says months and so what I have done is I have
put on my user sheet on sheet number two I have done the drag from January to
December and I've named this reign month and it automatically knows to put in the
months into our user form so that you can just drop down and get them and all
of these are just text boxes and then we have enter leave is where my code is
clear form just clears it out and closes it it's just unload me
okay so let's double click on the leave and so now let's describe what I've done
here I've defined name as a string RNG is a range rownumber move month as an
integer last start or leave start and leave end difference leave leave next as
integer okay so textbox if you have nothing in
there it's gonna say enter name now name equals trim so if you put spaces in
there it's gonna forget that and then then it's going to search for those in
column a and that's where we have the names of our of our our employees there
and then if it doesn't find anything it says name not found and then if it finds
it it records the row number that the range or that the name was found on and
so then we have our combo box now I have only done it for January February and
March but basically you could do it by just
adding the number of days so for if you were going to put in April you would do
this again but instead of 63 days you would add 30 30 days onto here so it
would be 93 days so you would move the month to start at 6 or at 93 now if it's
if the if the nothing is selected for the months then it's going to say no
month selected and the lstart which is the date you put in is your trimming you
this again to make sure you haven't got any spaces or anything in there plus the
move month and the move month is what you have you're moving it for a third
for February you're moving at 34 spaces so you've got move month and then what
it's done is it's um you have the L start and the L and define hit now and
in the sheets with the row number which were where it's found the employee name
the lstart the value equals V and sheets one cells
number Ln which we've defined up there the value equals V now the difference
between l and and L start if the difference is greater than 1 L next
equals L start and do while L next is less than L and so sheets 1 cells row
number L next equals value equals V and then it's in it's it's increasing its
increasing L next to Ln X plus 1 if L n-- x equals L n then go to end and E
and I've put in Y under the end loop and so here's the end if for this little one
this is the loop for the do-while and if and endi to create the button on my user
form enter leave a show form it is what's producing this to clear it out
which is my Clear button it is I've just made them all nothing to to close it it
simply unload me so that's how you create a user form to enter leave for
your staff attendance sheet please subscribe