We've added a ton of new features to the scheduling including time clock features so we know who's been clocked in who's been clocked out and as well as if there is any missing time clock and that's for both the month view and the week view as well we've have added a ton of other features and I can't wait so let's get started all righty I'm really glad to have you here after a week off thank you for the patience I was doing some traveling so we just skipped week but.
We're you've got a fantastic training for you today we're gonna cover so many different types of features and events and new i futures based on your request so we've got a ton of that I want to get right on it in fact I've made a list let's just go over the list real quick so we can see we've got a duplicate employee notification so if we have a duplicate employees we're gonna go over that scheduling we've got a right click to go over event let's just go ahead and show you that real quick if we want to create an event we can just right click or if we want to go to an event we just.
Right click click go to an event it's gonna go right to that event there so that's a really great feature I'm going to show you how we did that we've got a filter by position and filter by event type in the scheduling so all we'd need to do is select a specific position and it'll filter by that we also have filter by specific event types if they're contained we can do that so that's another great feature let's see what else I've added and I've been hard at work for you guys giving you the best employee manager in Excel so we've got a.
Lot scheduling we've got show clock in or out missing we'd go over brief at the intro so in both the month view and the week view we have the ability to show clocked in if their clock did if they're missing a clock out or if they're clocked out we're going to show that in colors and that's well in the month view except in month you were just showing them as colors and not text so we've got that ability as well we're going to show you how we went through that I've got a we've got affixed a ref issue on the employee archive if that.
Was an issue I'll show you how we did that employee ID cards is an amazing feature let's go ahead and through that basically we can get to it just by clicking ID cards and then we have the ability to display print and ID cards by clicking the print button it will print to the default printer whatever your default printer is set as that's my combines Snagit so we've got that up there and you also most importantly admins are gonna have the ability to customize this and by choosing from a host you have the ability to choose or hide a label you have the ability to.
We've got we fix an issue within events on the frequency and we've got sort employees on click I did Anna sort here a sort button we're just gonna sort our list alphabetically so we've got a lot to get to let's go through it the first feature I wanted to show you was the ability to we had a lot of questions about if there's a duplicate employee how are we going to handle that and so I wanted to make sure there was a warning if we let's say we have a facility called Lisa aim so if we go into another employee we try to change the name to.
The same actually it's EE and then the first name of leave so we're gonna get a warning right away it's gonna say duplicate of found please make the necessary connection Corrections so that is really an important part and I wanted to show you how it was going to change this name back to and okay so as soon as we change it back to an original name it's going to automatically update so that's going.
To go it's going to go away so we use conditional formatting on that so I wanted to show you how we did that and if we click on this cell here we see duplicate employee name found but it's hidden so we're gonna use conditional formatting to show that when it applies now there are two conditions two possibilities one is when we're on a new employee if we're on a new employee and we have aims and then we put in first name here Lisa it's going to also show.
Up whether it's a new employee or whether we change an existing employee so that's really really important that we have the ability to show for both existing and new employees so let's go ahead and take a look how we did that if we look in the conditional click here and click on the conditional formatting into the home conditional formatting and manage rules we're gonna see that we have here our conditional formatting b8 equal true b8 equal true that is the rule that we're using and when we format.
That all we're gonna do is we're gonna change it from blue to red on a fade a fill effect fade from blue to red then we're gonna change the font to red so that's the condition and that happens when b8 is true let's take a look at b8 and see exactly what is contained in that cell to help us determine whether we have a duplicate or not remember there's two conditions we have whether it's an existing employer new employee so we have to make those considerations now because here's the reason if we're gonna look up in our Employee List we're.
Gonna look for a name right we're gonna look here in the employee name here and I want to know if it exists or not if
It's a new employee for sure we have to understand that and if it's an existing ploy we also have to understand that too so let's go ahead and take a look that's what we're comparing it with so let's go back into the employee manager and take a look at b8 and take a look at this right here we don't need it that big we can zoom up okay so if all right we're going to start out with and there's.Gonna be two conditions kuku conditions the first thing is B six has to be false in this first conditions B 6 is false what is B 6 well B 6 is a new employee so that means on an existing employee so for this particular condition existing employed count if employee name that's the name I just showed you an employee list count if equals and why we do f6 comma I 6 what is that remember that is the last name comma space the first name.
Because that's the same format as we're using right so what I want to do is I want to look up this employee name and I want to look for something that is equal to F 6 comma e 6 so I'm looking up for this exact name and it has to be greater than one because if it's an existing employee then it must then it's already there right we're already there so it has to be greater than one in other words it has to be more than two times it has to be more than one time estin it.
Has to be usually it's going to be two if there's a duplicate so for existing employees we're looking for something greater than 1 greater than one because it has to exist already and another time so that's important we want to know that if it's greater than one if it's greater than one then mark this is true so those are the two conditions otherwise otherwise let's try something else we've so now we have accounted for the conditions if it's an existing employee if b6 is false but what about if b6 is.
True if it's a new employee then I only need to know if that name exists just once just once in this employee name if it exists just once then I need to mark it as true that means true meaning it is a duplicate so b6 is true that means it's a new employee count if Road we're gonna count if inside this employee name inside this range equals again f6 last name comma.
Space first name is greater than zero so the difference between new and existing is 0 greater than zero or greater than one existing if it's existing it must be greater than one if it's new and must be greater than zero that means it already exists and if that's the case market is true so if these two cases if this if this and this is greater than one then true if this is true a new employee and this and the count is greater than zero.
That also market true and if either of those conditions are not true then market false so that's how we know we just the important thing is to know that if it's a new employee it has not been saved in the list yet so we need to count if it's just one then we know but if it's an existing employee we needed more than one so that's how we do that's how we mark this is true so as soon as we type in let's go ahead and type in again aim so as soon as we type in this name and then we go to Lisa it's gonna.
Change b8 to true BA to true why is that because one b6 is false that's correct count we know we've got this in the list we know we have this plus a comma plus a space is already in the list right it's already in this list it's already here now it's twice so we cannot have that we can not have this toy so we must change it so so all we need to do is just change this to change the name and as soon as we make that slight change it.
Goes away so that's a really good way of how we can differentiate and how we can see very quickly if there are duplicate employees whether it's for a new employee or an existing employee so that's how we handle that all right grant we've covered duplicate employees let's go ahead and cross that offer let's change that so we know we've we've made that green let's go ahead and change the color on that we've now let's go scheduling right-click go to event.
This is a great little feature back into the events and back into the scheduling so first both on the month view both on the month
View and the week view we have added a future a right-click event and let's go ahead and click on an event right if we click on something other than event we're going to get something that's other than event this is the time clock or this is blank oh we would just get in message please select a valid event oh so we need to make sure that we actually select an event like this one right click go to event and that's going to.Bring up right here this is a very easy macro the first thing we do is we want to create a right-click option right click option and we can do that here under we've created a yellow little triangle here as an icon and then go to events go ahead and see how we did that and keep in mind before we go remember we've covered in Prior trainings 60 spaces over we've got the event ID so this event ID is important if this for some reason is blank we're gonna return out this is not an accurate event so the.
Event ID which is 60 columns over to the right is very very important we need that so we know which even to pull up on the event sheet so that's critical so we're gonna need that anytime there's no event for example this we're just gonna get that message please select a valid ID a valid event alright so we've got that covered now let's go ahead into VBA and we're gonna start out with the right-click and we've added right-click.
And before when we created the new event but we've added on to that so we're gonna go into the VBA into the developers tab if you don't have it all f11 will get you there quickly and then we're going to go into this workbook and that is where we're gonna start out there and basically what we're going to do here is on its like before right-click event before right-click event that's what we want some to do something and the first thing wanna do is we want to delete and leave it we can add one more here if we want to because we've added another one but I think it.
Should be sufficient it helps remove duplicate entries when we delete we always want to delete it in fact I've got two of them so I may I may want to add one more to this just in case it gets duplicated so what we're gonna do is if the Act she is she ate meaning if this is the scheduled sheet then right click and call events so we went over that before cuz we have added some right-click events already into our schedule remember in a previous training we did at the add leave event where we can add an event based on a right-click event.
Today we're going to focus on the go-to event and basically the reason that we have the reason we have that option is when we click another sheet and we right-click we don't want in fact here's it is you see why this is appearing it should not appear because I need to have that delete I need to have that delete right there before so let's show you how important that it alright so let's go back into the workbook and I'll show you why that is important why we must delete it we deleted the other one but the other one so we've got to create one more I'm gonna delete this I'm going to.
Duplicate this right-click and I'm gonna paste it right there and but I do want to update the name with the right-click and name the actual name of our right-click in fact if we click the right-click menu we are going to see that we I have added one more called go to event go to event so all we need to do is copy that go back into the workbook and we want to make sure we delete that button when we go into another sheet if the sheets not we want to delete it so now when I right-click.
Here it's not there anymore because that's why it's always important to delete it I only want this appearing on the scheduling sheet right here I only want go to event on the scheduling sheets and we click any other sheet it's automatically deleted okay so it's not there we only want that on that so that's how we can do that we always want to make sure that we delete it at times so this says on so we're gonna delete it regardless regardless if we're on sheet or not why because when we when we are on she date this macro right here right.
Click call events that's gonna create our right-click menu so we're gonna so we want to delete it first because when we're on sheet a is gonna create it if we're not on she date it will not create any right click that now let's go ahead and take a look at this macro this is a macro that we visited before but I've added onto that some features so let's take a look under the right-click menu here in the module we were just there this is called the right-click event and now previously we did add these which was going to we did.
That add leave event and now we're gonna go to go then we also want to delete them here just in case they're existing we want to always delete them if we don't delete them it's gonna create duplicates and we don't want duplicate items in our right-click menu so always deleting before we create always leading and if for some reason they don't exist we've got on our resume next just in case this is not existing it's gonna skip because we have this on air it allows us to go to the next line.
Just in case it's not there or not found now those we've created this previously this is our pop up to sense the leave button and here that here set go to event button we've created this go to events that go to event button this is a command bar button command bar button previously we had a command bar pop up what's the difference between the two of them right click a command bar pop-up allows us to give a pop-up just like.
That but when we go to a command bar button is simply just a button there is no pop-up so that's how we that's how we do it when we want to just a button we create a command bar button we want to pop-up we click a command bar pop up so we've differentiated those in our dimension statement right here command bar pop-up and command bar button and this time we got it a command of our button so we've deleted it said we're gonna set the go to event button as a command by ourselves control add before to why before too because this is the.
Top position and I want this in the second position right below and it's gonna be a Cammisa control button not a pop-up but a button so here we've set it up and now what we're gonna say is with we've created the leave button here this was previously right so we've done that previously this is how to create the leave how we're going to create those multiple leave where we loop through those we did that previously but in this case all I've added is on its this additional code right here and this says with the.
Go to events and we're gonna set a caption the captions gonna be called go to event I'm gonna set a face ID of 1812 face idea is just a an ID so if we were to do a search on Google and we could see that you can you can find out many different ways to do that let's go ahead and just show you what we could do is there's some different ways to do that you could do let's take a look let me lower the screen just so you can see different ways to find that face ID because you may want something different.
In your face ID so let's go ahead and dump excel vba face ID and if you look at the pictures there's some really good images here and it kind of gives you you want something with a number like this it's kind of small but and the numbers that go along with it now you'll see here you see each one of those has a number that goes along with it and there's a lot of them you can search them you can save them but basically the idea is that each one of these numbers is assigned to a specific icon so when you select one you know then you could.
Use that number correspond so that's that's how we get those and there's a there's a few different ways there's a few different programs that can get you a face any but it's pretty much simple to look it up on Google you may want to save these pictures in your file if you're gonna be using them often so I've done that as well all right let's move back in so now we know how we got that face ID all right so here we are now back into it go to event button we got a set set the caption of go to event that's the caption and the face ID is 1812 that is the number associated with.
That yellow triangle option that I'm the yellow air option that I've used and of course on action what do I want to do when the button is clicked well I want to run this macro I want to run a Mac are called schedule go to event so that's the background let's go ahead and dive into that specific macro and look at them and we can find that matter in our scheduling logical module everything is organized so we've got everything named scheduling macros and we're gonna go to again called macro call right here.
On the top schedule go to event and the first thing I want to do is I wanted to mention the current row the active row as long and the act of calm I need to know the row and the column those are important and then we're gonna dimension the event ID as long as well the event ideas whole number so those are helpful so we've got those three as long and we're gonna determine the active row a CT row as our active so row and remember active cell we use when we're off the sheet when.
We're on the sheet when we're doing coding on a sheet we use target row target row four on the sheet off the sheet we use active cell row so that's the difference that's why you see sometimes you see active cell and sometimes you see target target means code on the sheet code off the sheet in a module weaves active cell active cell row and active cell column so we define those we're gonna need those now we have to check remember that remember that event ID I need to check to make sure that that's not empty and remember.
That's our current row the current row plus our current column plus sixty columns to the right sixty columns to the right remember that here's our event we move sixty columns to the right and we're gonna see an event right there that's the number sixty exactly so what I'm gonna need to pull that number if it's blank and then we're gonna say this is not a correct event so we need to make sure that there's a number in that existing row sixty columns to the right.
That's where we store our event ID all right so we need to check for that it's the first thing we want to do so it's if cells and we the reason we use dot cells is because we've already defined sheet eight up here if sheet eight cells active row the current row that row won't change plus the column plus sixty if that value equals empty then we know we not we cannot go to him to a specific event if there's no event ID so in that.
Case we need to put message box please select a valid event to open to to I guess we could say to go to event okay so and then exit sub we can't move any further if we don't have an accurate event ID so if example if we click on a white space here and we click go to event we're gonna get that message box please select a value event to go to event that doesn't really make sense either all right let's go ahead and just put it back the way it was to open all.
Right next up event ID now that now that we're sure that this contains a value we can set the value to event ID that's ideas equal to the cells the active row plus the active column plus 60 so that is our event ID next up we want to activate sheet 5 that's our events events sheet 5 so we need to activate that we want to make sure that's of active and then all we need to do is just two things I need to put the event.
ID into range b4 before and then I'm gonna load run the macro load event so if we go into our events sheet right here we slide on over and let's to b4 and that's we're gonna put we're gonna put the event ID there and all we're gonna do is load it up so if we were to put in 20 and run the events it would it would load that event or any event that we put would automatically load it so it's a really really handy handy feature let's go ahead and take a look at how that works so when we go back into the scheduling we select on any event let's.
See for Tonya angel this event Tonya's vacation we right-click it go to the event and pulls up Tonya's vacation just like that and with same thing for the week view we have that same feel because again even in the week view 60/60 columns over right we've moved it over again 60 exactly we have our event IDs here so the same macro works just fine for weekly as well so we right-click go to.
The event again Gabi's car trouble right here so it's a very very easy way to go to the event right from the schedule you see it's not a lot of coding alright great I'm glad I got you let's go ahead and move on we've got so much to cover we've got so we've covered that so let's go ahead and color that to know that we have colored it with green scheduling filter by position let's move on that next we've got those two filtering by position and by event types don't want to show you how we did that all right so let's go ahead and head into scheduling and take a look at that and again what.
I'm looking to do is I want to filter if I only want to show a certain position I just want to filter and show only those positions that are managers I only want to show a perhaps a position that is an owner or I only want to show specific positions I may not want to show all the schedule and of course this would halt true for both the week view and the month view so the week view the same idea managers and just so this way I can limit the schedule specific we have a lot of employees we may want to filter.
It down by just specific employees based on their positions and once again the positions are based the list the list of position types are based here in the admin once the admin we do have our employee settings in here we have our list of positions so you can add to that list here anytime you want in the employee manager sheet once again we can select any type of position here simply by changing or adding here we can set the position of any employee and this gives us the ability to filter it out so.
Let's go ahead and take a look at this let's start on these positions and show you just how we did that the first thing of what I want to do is I've got a list of employees here but I want and really what I want to know what their position is and I want them listed out on this table so I've got them what I did is I basically just used what's called a helper column and if you slide over here to the right into column 172 FP I've used one index match to basically determine what their position is in place it here the first thing I did is.