Beautiful but it's much easier to create and it's also looks great so let's go ahead and get on that this is gonna be the first part of many parts to this employee manager we're gonna be adding lots of features in fact most of those features are gonna come from your suggestions so it's gonna be great if I can get your feedback on this and so I know exactly what to add in what you want to see and so it's gonna be based primarily on what you want to see an.
Employee manager so we're gonna start with something very general and then we're gonna add features so please make sure whether it's Facebook or YouTube or any other social media to go ahead and add in your comments what you want to see what you features you want what's important to you so I can try to get those in alright first up we're gonna add a header that's what I've been doing here as I'm talking and we're just going to create a fade and now we're going to use the three colors these three blues is our primary colors and we're gonna now we're gonna stretch from medium blue.
To a little bit lighter blue on the fade so that's going to be our top row here and then our second row here we're just going to extend it to our but it's not so critical how far we go we can add to that later and the second part of our faith is gonna be one narrow down to that and so we're gonna fill that with our medium to light medium and then to our very light and this is going to be our primary background color so we can set of that fading it down from darker to lighter now we can go ahead and set our background color we'll just go and go down we're gonna go down a lot.
In these cases because we're using a tab so we'll go down to two whatever here and then just set our primary background color as this background color here okay so now we have our fade let's add our title we'll go ahead and add and we're gonna call this employee manager and let's merge and sell these let's say across maybe to Jay we're gonna increase those and merge and center that and Center it up there and now we'll increase the font and also let's give it a new font perhaps Arial rounded let's.
Take a look it's something something a little bit nicer than just your standard okay this should work all right so let's go ahead and bold it increase that a little bit and now I also want to increase these two let's just say 18 or something like that increase those a bit all right so that's going to cover that and now we've got our our basic format and what I want to do is I want to in row two I want to add an employee here and so this is going to be employee and.
Then what I want to do is I want to have the user select from a box now we're gonna add this in the little future but at least don't want to set up the main parts and then maybe in I I want to put the employee ID and then and then in jail we're gonna put that ID so let's just put zero zero five four now just as a placer or let's do one zero zero five and so I'm gonna place this left this gives me the basic framework of the screen that we're gonna work on and then.
I also want to right and left justify that I want to increase these just a little bit to maybe twelve or fourteen or something a little bit bigger than that perhaps thirteen okay so this is going to be our our basic header here and we can go ahead and add format those cells we're going to use our basic color of border we're gonna use this blue here's our gonna be our basic border color we're gonna wrap it all the way around and then use a dashed in the middle all right so that's covered we're gonna leave Row three.
Blank and we're going to start our tabs in row four so we're gonna set our horizontal tabs let's start out with general info in our first and then I'll want time clock history there's gonna be our tabs I want payroll history and then I want payroll details payroll details is going to be our big one we're gonna add vertical tabs in that one all right so this is our basic tab structure let's go ahead and center that and I'll put some.
Borders around it now so I'm gonna use a little bit larger borders same color of our borders so let's go to that same color blue and we use it not the thinnest border and we'll just cover all the borders for now I want to give it a background color I want to get what we're gonna do is we're gonna give it a background color that color is going to be the non active color and then with conditional formatting we're going to give it the active color so we can format those cells using pretty much the.
Same colors that we did before under the fill here and we'll we're gonna fill effects and then what we'll add is we're gonna add a light to dark so let's go ahead light and then a little bit darker to medium using those same color blues alright so now we've got our basic tab structure and now let's go ahead and take care of our main section here and I want to each section I want to create basically let's say about 20 rows so let's go down to 24 just put a border.
Around that so let's format the cells and let's put a thick border around that
And then we're going to duplicate that basically that process for about all for each of the four tabs this one here and so for each of the four tabs I want something just like that and I'm we're going to go from five to twenty four twenty five to forty four and so on I do that about four times so we'll copy and paste that down all the way for each of the four tabs alright so that covers it to 84 we're.Gonna create once beyond this but these are the four tabs this is our four tab structure and this is our general info this is our time clock info or payroll history so what I want to happen is when we click general info I want this section to appear when I click time clock history I want this section to appear let's just put a general just a temporary label in here general info so we know what section is and then we're gonna work on time clock because it's easier to work on once we get the tab time clock history this is gonna be replaced and then payroll history this.
Just helps me label the section just temporarily payroll detail okay so now we have all four seconds now we have all four sections so what I want to happen now is I want when I click generally felt I want five to twenty three to show up and so on and so forth for each of the three and we could do that with VBA I also want to mark which row which column has been selected so that's let's put that right in here selected column and then we're gonna put that ready let's just say it was five for now and on I also want to know the selected row.
We're gonna use that a little bit later on but I want to just put a placeholder here so we have that too so we have selected column selected row and what I want to do is I want to add conditional formatting so for example if this column this is column five this is column six column seven whichever column is selected I want to place conditional formatting to give us that effect and on what I want to do is I want to blend the bottom down to here so I'll show you it's relatives to work in a highlight what we want to place our conditional formatting in we're gonna go into.
Conditional formatting and a new rule and we're gonna base it on a formula so it's going to be equals and then here right this is our selected column equals column and that's it so that's it so now the format so basically when we select the column if this is the column it's gonna format it now when we format it I want to blend the background colors so we're gonna give it a fill effect and what I want to do is I want to start out a little bit darker and rim and go all the way to which is our background color remember our back because we want that.
Blended in and one further step we don't want the lower border to show so we want to clear that lower border that's important all right so let's go ahead and click OK ah that's perfect you see how that works now what if we're gonna change this to six perfect that's the effect that we want you see how easy that is you see how quick and easy that is seven okay now shapes looks a little bit nicer but this is just so easy so now all we need to do is get VBA to change that every time we select III H it changes this so.
That's one step let's do that right now into the developers tab Visual Basic and we'll go ahead and on the sheet and we're gonna use selection change so worksheet selection change because we're actually making a change based on selection we can lower this so we can see both at the same time efore through h4 right efore three four if we make a change there if not intersect target and remember this is a column i see people putting a period a lot comma range e4 through h4 and you.
Need to end parenthesis twice is nothing then then what okay remember here if not and nothing those are double negatives that cancel each other out when you see two negatives get rid of them basically it's saying if a user selects efore through h4 then do something that's all i know it's confusing but if you just when you see two negatives like this get rid of them remove them out of your memory and then you can easily see if the user intersects a target between efore and h4 if they select then do.
Something and what are we gonna do well this is relatively simple I want to put the column in b2 so range B - dot value equals target dot column that's it all right let's go ahead and test that out general info nice time clock but it here's the problem it doesn't look I mean it looks fine but you have to select away then you can see it so why don't we select F - just take it away because we only selected I want I don't want the selection to be in the cell.
That we've just looked it I really want to get away from that we don't want to we don't want the user to stay in that cell we really want to be out of it so
Let's select F - after that and we'll add that to the code range F - dot select all right so now let's look at it all right that's a nice effect okay that's great and C okay that's great that's the effect we want so we've got the tab effect but now what all I need to do now I need to actually hide and show columns based on this so we actually can see it so let's take a look.At that into what we're going to use a macro to create that so into the VBA let's go ahead and insert a macro insert a module and we'll call this tabs module because we're gonna be running our tabs so properties here and just call it tabs macro now make sure that whatever you name this module you do not name the you cannot have sub tabs macro here that's that's going to create issues we got the.
Same module name and the same acronym cannot do that so we don't want that alright so let's create a macro based for this horizontal so let's go switch horizontal tabs and that's going to be the name of our macro so we're gonna using vertical as a little bit later on so first we wanted to mention the selected column as long and we're gonna do first row as long I need to know the first row I'm doing.
Them separate lines I'm doing it properly this time first row as long many times you will see me do something like this first row right dimension them both that's kind of improper it works but it's improper so today we're gonna do it right for a change dim first rows long okay so two separate lines that's technically the best way to do it so I often don't do it but it's always good you get in good habits so selected column equals what is the.
Columns gonna be the active cell column active cell column that is the column that we want to set we need to know the column for several reasons all right so we're gonna focus on sheet 1 so with sheet 1 that we're gonna do all of our work in sheet 1 what I want to do is I want to hide all the rows I first want to hide everything because we don't know so we're gonna hide everything from from 5 to 84 just going to be hidden and then mortgages are going to hide everything and then show only what we need to show so the first part is we want to hide everything 5 to 84 so let's do that dot.
Range 5 through 84 entire row duck on bro that hidden equals true ok so that's gonna hide it now let's show now what I want to do is I want to show 5 through 23 24 I want to show that on the first on the first one on the second one I show but so what here's what we need to do this is Row 5 excuse me this is.
Collin 5 column 6 column seven call them 8 so we have these 3 columns right equals column just to show you those column numbers five all right so you see there five six seven eight okay so I what I want to do is if it's called five that's been selected I want to start at row we always know it's going to be twenty rows so we always know it's going to be twenty rows that's that's of course that's going to be set for.
Everything so we can figure out the last row is twenty four we just add nineteen we always know the last row but we don't know the first row in this case it's five but what if we select column six in that case it's going to be twenty five so we're just gonna and then in the next case it's going to be 45 and then sixty five so the idea is if I have five I need five if I have six I need 25 so I need a formula to convert seven to 45 so let's write that formula now so I need.
If it's five so let's do that so we can easily convert the column to the first row that's what I want if they select column seven I want to know the first row is 45 so let's write a formula for that we can do that in VBA we can say something like the first row is equal to five right we're gonna add five plus and I'll explain this in a moment + and then we have the selected column column minus five minus 5 times 20 so we know for.
Example we know let's say the selected column is 6 for example let's say that's like to Columbus 6 I need to get to 25 I need to get from 6 to 25 or from 7 to 25 if I subtract 5 from 7 I'm gonna get 2 if I'm going to get 2 times 2 times 20 is 40 plus 5 right if I select if I subtract 5 from 6 I'm gonna get 1 1 times 20 is 20 plus 5 so that's how we can do it we subtract 5 8 minus 5 equals 3 3 times 20 equals 60 so all we have to.
Do is multiply times 20 and then add 5 again that's our formula so all we have to do in this case let's go ahead and we got one extra prince he's there we don't need that so all we have to do is subtract five there we go all we have to do is subtract five right so if the select column 6 minus 5 is 1 1 times 20 and then that's it that's all we do and so that we just subtract 5 and multiply times 20 and then we add 5 to.
That and so if it's the first column this is going to be 0 if it's the first column 5 right 5 minus 5 is 0 times 20 is 0 right plus 5 so it's 5 so that is how we get the first row 5 25 45 and 60 so that's all we need to do that little formula is going to help us determine the first row so we need that and then we know the last row of course is going to be plus 19 so we can continue with our code now that we've determined the first row just through some simple math so now we can we can unhide the specific.
Row so dot range and then we have the first row we don't need dot value that's just automatic first we don't need the coats because it's a very first row and quotation and colon and first row plus 19 right that gives us a total of 20 and then dot entire row dot hidden equals false now we've hit it there we go that'll work all right so let's take a look at that.
It looks ok for now and now what we have to do is take this macro copy it over and bring it into our selection change which is here so after we've selected it enter and then run that macro right out selection change and let's take a look at that okay let's get rid of that space all right now let's run it okay great all right but look our look at how our lines didn't didn't go well why is that.
Let me show you that is two the tabs there's a few Corrections we need to make since we're selecting f2 it's always going to be call - as the Selective so we need to make a quick adjustment and set that to be - so let's let's have a select to call an equal dot range B - so and then of course we're gonna need to put the width above that or below that or put the line above that all right so now we've got that now that we've done that we can go ahead and take a look at that now we've.
Got our tabs and our are working perfectly but look at this line it only works here here it's not working well because this is the lower line so let's remove those let's remove all of these lower lines here let's remove let's go down here let's remove this one we don't need it we only need it on the last one we all need it on 85 let's unhide it and remove and keep all but the last one unhide all right but let's remove the last let's remove this one because we don't need that format cells' remove the bottom when we don't need that anymore we're just gonna use the last one and.
Click OK and keep and then remove this line here sorry it's a little bit off the screen there I know formatting the cells I'll bring it up for you and remove that one and then I'll getting also this one we're gonna remove that we don't need that I'll bring it up here okay we're gonna keep the last one I want the one with 84 but in fact I'll.
Show you there's still an issue we still have to fix let's go ahead and look at that okay that works that works that works but we're missing the line here so let's go ahead and now here's the issue watch watch this if we format let's go ahead and highlight these cells all right you see when we select all it runs I don't want that to happen let's fix that when I select more than one cell I want to exit out we can do that here.
Let's do it right under here if target count is greater than one then exit I don't want anything to happen exit sub now when I select additional ones nothing's gonna happen so now I can select lot and nothing's going to happen so we've covered that so there's a few issues here look you see this top row is looking it's not working right right so let's fix that now the issue is that only this row I know it looks the same.
But let's take a look at this format the cells and let's remove this one and then but let's add it here format the cells and now I the lower one let's add it here now let's take a look all right now we've got that now it looks like we've just about got exactly let's put these in the middle all right put them in the middle and and now I like it but I want to make this I want to make it stand out.
More let's take this and make it blue and bolt the one that's selected so it stands out just a little bit more conditional formatting manage those rules edit the rule format and let's go ahead into the font bold and I'm gonna give it our our blue color which is here okay good now I like it now it looks good now we know exactly what we want to select it very nice alright now we can start now you see each one we have.
Separate now we can easily let's raise this up now we can easily go in and see which one now we can start working on our individual tabs because all we have to do is click connect them so let's go ahead and work on individual let's add it what's that a little bit of a look to this we'll add some pictures to that I've got some here I want to add a placeholder for a picture I've got some pictures I will add buttons later on in future trainings but let's go ahead and add some look I wanna I'm gonna put a picture in the general info like an ID.
Picture little placeholder just for now and let's give it an icon here but that unless color this are that same color or some somewhere maybe a little bit darker okay so now we've got that and bring it here all right bring it a little bit larger and increase this font so we we have something a little bit bigger that's that's good all right so it's looking good now there we go that looks even.
Better okay so now we have our employee manager now we have our general info our time clock history now we can start filling out these fields and now we can start getting into and adding our fields we can add start here into the general info and we'll put in last name and then we'll leave space for the field and then first name we're gonna have that and then maybe a lot of fields that you want to see so please let me know address now.
I'll leave this whole line for the address of maybe we don't need this anymore and next up we'll put in city and then over and state will format these in a bit next up let's have zip code and then maybe a cell phone number of course you can change these as you see fit it's quite easy and then cell phone maybe we'll put an other phone here just any general employee information that you might want of.
Course we need an email for the employee and maybe position I'm gonna leave some areas empty too because you know as we build this throughout the weeks we're gonna want to put in some and maybe status like if they've left the company or something we want to know that and then that's it for now we have lots of space to add this and we do need to add this 85 I need to add an upper border on this one so let's format the cells and then border I'm gonna add the upper.
Border now here it is here but if I strange you see it here this is what's confusing watch wait let me just cancel out of this no change right watch this format the cells okay I'm just formatting even though it's off the screen we're just going into format I'm gonna erase the cells and then I put them in again just like that that's all I'm doing watch it's there this now why is that I know it's confusing it's like because in actuality it was the low war line of 84 it was not the upper.
Line of 80 85 you see there's a difference believe it or not even though it looks the same so if you ever run into that issue hide it and show it or delete it and remove it and it'll said but so it was the lower line of 84 now it works you see how that is it's kind of an Excel oddity but just clear it out and show it again that's it let me do that every weekend I want to show you that it's not here right and let's I'm gonna open it up unhide so let's go down to 84 I want to show you what's really going on here because it's kind of an important it's a.
Very frustrating issue 84 has a lower line right-click format the cells 84 has a lower line right 85 it looks like it's got an upper line but because of the hidden format the cells so this is actually 84 slower line it's not 85 upper line hide it show it done that's it now when we tab over click on that it shows strange but it works.