Create Horizontal AND Vertical Tabs in this Excel Employee Manager [Part 1]

hi this is Randy with Excel for freelancers and thank you for joining me in this week's we're gonna cover a multi tab both horizontally and vertically in which we're gonna start building from the ground up so you can see every type of detail every feature every conditional format as we build out the application before your eyes so it's gonna be a really amazing training it's gonna be part one of an employee manager that's gonna extend for multiple weeks so I'm looking forward to bringing this to you let's get started all righty I've had a lot of requests for an employee manager so it's about time we get one done and I wanted to show you a versatile to have both horizontal and vertical using only cells in no shapes in the past we've done ones with shapes horizontally and vertically as well but I wanted to show you one with no shapes and just using the cells it's a actually an easier maybe you know not as 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 okay so we're got that now we understand because 85 is always shown right 85 is always shown so if it's 84 is lower line it's not gonna show but if it's 85 upper line it's gonna show so that's the kind of a strange trick but so that's why so I don't know if if if you've understood that it took me a while to figure out you know many times I just didn't change it and I thought why isn't it showing up but if I simply if I simply reset it to show 85s upper line it'll show so it's kind of an important issue and I think many of you have run into that at times okay back into the general let's format these now we've got our basic information we're gonna put of course the fields in white and in future we're gonna add tab orders to this now we've done tab orders before but we're going to let's do that again we're going to add tab orders so that we can control how the user tabs so that's going to be important I'll fix that in a second so state and first-name let's make those white and let's add some borders around it of course we don't want status bow fix that let's drop this down and then we're gonna add some borders on it format the cells and what I will do is we can use our same blue keeping with the theme and we're gonna add a dotted line on the left side and we're going to add on the right side just the board of the thin border okay now other than status now let's do our field labels here we're gonna format those including status EML cell phone state and address address we're gonna bring out format the cells I know that's off screen but I'm just clicking format cells that's all I'm doing it's the same thing as clicking the font here and let's go ahead and run that we're gonna add that border that border is going to be solid on the left and let's leave this let's put salt on the left and we're going to put the dotted line on the right and then we're going to put the alignment I want that right justified right so there we go okay so now we've got our fonts and now let's just fix this border here we want that always solid border so format those cells and put in that solid border on the right we want that okay so we've got that we've got to look let's merge and center this bring out address cuz that's a larger field merge the center and then left justify also we want to left justify these fields and we're gonna change fixed status right now left justify those status should have a background color of blue all right letsa now we're gonna be using this a lot so let's set this as a format style so let's look at the Styles cell styles I'm gonna create a cell style new cell style and we're going to call this field label this way we can easily add it later on and we're gonna call this field so let's go ahead and do that as well a cell style and a new style and we're going to call it field all right so now we've got that now we can use that in the future add a border to this using our same blue color now we've got it now it's looking like much better all right so we've got that got ours and we let's color these in our blue color the fonts give it our blue color so it looks the same now we can go time clock history now we've got our general info and now let's focus on this picture we can move on it's only going to show up we're gonna make sure this picture only shows up on the general info not the not the time clock history all right in time clock history what I want to do is I want to add a an advanced filter so that we can we have a limited area here for the time clock history all the data is going to be stored in future versions all of it but we only have I don't want to show a little bit of it here let's say 20 rows or something or 14 rows I don't want to show a little bit so let's add a filter on to that so for example if we add a date filter in the future from and then to then we can add a date filter here so let's click on those and use our cell styles that we just created field label here and then here and here we're gonna add our cell styles and this is going to be labeled that we just created okay so we got our from in - that's good I want to put in dates here and then as I enter dates I want our table only to display certain information that's at our table now date will start at first start time and then of course end time we also need to know total break we're gonna add more information in our actual time clock history but this is more of a summary so in other words we'll have more baby break in breakout I'm not sure exactly it's it's gonna be up to you so so you got to let me know what you want to see regular hours and then maybe overtime hours this is a little bit big we don't need this one here so big all right let's format the header of our table so we'll give it a format format those cells I'm going to use our same color combinations very similar the font will use bold and the fill let's fill effects and add a fill effects fading down from our medium to our light so that gives us our fate and now let's add some borders to that I want to we have our outside borders but let's put our inside borders of not this one this one and this one and our we can put our table okay that'll that's good and Center this so that our headers are centered now we can add our we can we can remove this we don't need this now we can add our data table so let's do that here we're gonna first of all we don't need any color we can use white or no color either one it's gonna be fine white is fine and then I'll just put in some dotted line borders dotted line in the middle we don't need any oh we're gonna add in some alternating color on conditional formatting manage rules new rule we're gonna use an alternating color similar to how a table is formatted equals mod and I'll go over the whole mod and that formula is row comma two equals zero I use autohotkey to type it automatically for me let's format this we're gonna use again our fill colors but I don't want I only use a little bit lighter than our standard color so let's go into more colors and choose a little bit lighter there that's a little bit dark okay that's a little bit lighter now we have our table formatted okay nice so now we've got that we know let's go into the payroll history let me copy this because I know we're gonna do very something very similar in payroll history so payroll history and I'm just gonna paste it right here but payroll oysters gonna be a little bit different but at least we don't have to recreate the table again on that and it's going to be in this case it's going to be our payroll date and we want total earnings total deductions total edition sometimes we get additions like bonuses and then net income there may be others something else that we want to run sure yet let's keep this blank we have the ability to add something here here in the future maybe total tax deductions maybe we can break down deductions into like tax deductions and other deductions maybe we'll do that not sure yet tax now let's do that tax deductions because we'll break it down that way we have the space so tell other deductions additions and net pay net pay and that income is fine okay net income all right now we've covered it we may change those in the future I'm not exactly sure but I'd say it's at least we've got it ready all right so now we've got that let's move into the payroll detail and in this one what I want to do is I want this there's so much you know when you when you add payroll there's so many fields we have many more fields than we have this space this is where we're gonna add our vertical taps when I select a vertical tap I want something different to show in here when I select another one so let's start that out let's build that vertical tab first we want to label select options and then I want to go over the earnings the first option maybe earnings detail and then the next option would be maybe tax information because we have always there's always tax pay deductions this will be multiple paid deductions like if you have specific page reductions on your paycheck and then maybe of pay additions so in this case what I want to do is I want to create again I want to come on a select here and I want something different to show up I want to select here and something different again we can use vertical more more spaces down here to achieve that let's set this up let's go ahead and set our format first this one is going to be our header and it so let's bold that and Center that and then we can give that a color like let's go ahead and we want to give that a little bit different this is not gonna be for a clickable so we're just gonna set that as a set fill that and fill effects we can give that are one of our colors here and we'll give it a vertical and but in fact these are gonna be more of a horizontal so let's set those up in fact let me set a border so we can differentiate between those because and we may add more vertical tabs in the future if we want this is what's so great about it we can use such a small area but use tons and tons of data which is a really great it's very user friendly too so it's a really really great system once you can learn it and it's actually relatively easy to program we'll set the border on this one so we can differentiate between the title of them okay so what I want to do is when I click here but let's give these a color format cells and now we're gonna use the same principles so except this time it's going to be horizontally so we're gonna give it a fill will go from medium dark to middle color and then use a vertical this time we're using a vertical and I want to go from dark to light so now okay so now the idea is and give these some borders inside here format cells and we'll give it a lower border just a normal border here okay so now we've got them separated by borders so the idea is when I click on here I want something show up here and I want this to show up again we have selected row here so let's put that in let's say 67 for now and what I want to happen is I want to add conditional formatting that shows that 67 has been selected so let's look at that format cells what's that conditional formatting manage the rules and new rule and all we have to do is set a formula that b3 equals in this case row and now let's give it a format so we'll go ahead and this kind we're gonna give it a fill effects also vertical but a little bit lighter so it blends with the background so the first color is gonna be that medium and the second color is going to be that this is our background or giving it a vertical and from from darker to lighter and one more thing I want to remove that border so I'm gonna go to the border that bright border should be gone because we want to clear that out okay now let's apply that and click OK that's the effect that I want that's the effect so if we were to change this to 68 exactly that's what I want and maybe we're gonna make this bold too again same consistency bold and blue so let's go back into here manage those rules edit that rule format it go to font bold and the same color blue that we have that way we have consistency throughout our theme nice that looks good so that gives it a nice professional software look and that's what we're looking for now let's go ahead and get the code in there so that when we select that we should have those different sections set up and so the idea in this case it's gonna be the same for the horizontal we're gonna add additional verticals down here below 85 and I want to copy this area here so that it's really the same exactly so that it looks the same so let's go ahead and do that we're gonna copy 65 we're going to place it now we've got four different tabs here so we're going to do it two four times one two three so now we've got four different ones that looks good so the idea is when I click pay deductions I wanted to show up here when I click tax information here and so on and so forth so we had to duplicate that in order to do that let's go ahead and get in our highlight now when we select it we could be selecting anywhere from 66 all the way down to 129 so the selection change is going to be from that range so we want to make sure we add that in so into the developers let's go ahead and add that in and continue on with our selection change we've got here for our horizontal let's add in some code for our vertical again if not intersect target comma range II 66 through II 129 that's our range so if the user makes a selection any one of those then do something is nothing then but now we have to make sure I don't want them if they select this if they select this select option I don't want anything to happen so we want to make sure that they only selecting DS and if they select nothing we don't want anything to happen so let's put in a check this like nothing nothing should happen if they select the word select option nothing should also happen as well so let's add that in to the code just in case if target dot value equals empty or target dot value equals select option then exit sup okay so we've got that covered just to make sure in case that here then we're gonna exit the sub otherwise let's run a macro let's call it switch even though we don't have this macro yet switch vertical vertical taps that's going to be our macro so let's copy that and go back into our tabs macro and we will add it in sub paste now we've got ourself alright in this case the first thing I want to do is let's go ahead and add in some information all right first thing we're gonna do is we're gonna dimension the selected row as long we need to know that we also need to dimension the first row just how we did before no spaces right now we also need to know what the selected row is now it gets a little bit tricky here because what I need to do in this case and if it's 66 if they've selected 66 right row 66 we want to show from 65 through 84 if they've selected either 67 let's go over that again 66 86 106 or 126 so they could select any one of those right then we must show from here only that would be 65 to 84 let's let's fix that I'm gonna create this all the way up here right up here bring this target sub bring it up here so that means anytime we select more than one row we will not get the book again like that okay good so if they select 67 87 107 or 127 the tax information should show so what is common on all of those the last digit this is one way to do it six seven eight night so I need to know what the last digit I need to know is that six is it seven is it eight or is it nine right because even if they select pay editions because they could select any one of these rows so if they select pay Edition it's gonna be 129 pay editions is always gonna end in nine right 129 109 89 or 69 so let's get the last number that way I can know that means regardless of which one they're selecting I know it's the last item on our list so I want to get that we can use that in the code we just need to know what the last row so we can do something like this let's go into our tabs macro into our vertical here our selected row it's gonna equal right-right is gonna get us the rightmost character the rightmost number that's what I want active cell dot row it's the row that we've selected one that means I only want the last character only I don't want the last two characters I want the last one character minus five why are we - five it's gonna get us one two three or four the select rows gonna be one two three or four why is that let's go over that again if they've selected 66 our last character six right and if we - five that means one right six minus five equals one 7-5 that way I know they've selected the first the second third or four so I can we extract one two three or four regardless of which one they select that's what I want to know I want to know one two three or four so I need to regardless of the row so that's gonna help us again if they've selected this one 129 we're gonna get the last character which is nine in this case we are going to minus five nine minus five equals four I know they've selected the fourth one once we know that four we can then easily take a formula and show only one twenty five through one forty four so let's do that now and continue with our VBA code we've got our selected row so let's look at that now let's continue with sheet one with sheet one we're gonna be focused on there well first of all we need to hide everything so let's do that dot range sixty-five that's gonna be our first row we want to hide everything right we want to hide all the rows first that's always important 65 through 144 144 that's gonna hide everything dot entire row dot hidden equals true so we're gonna hide everything all the rows first then we're gonna show only the rows that we're focused on so let's do that we have to get our first row first let's go at first our first row we need to know our first row first row equals sixty five-plus remember our selected row selected bro minus one times twenty we'll go over this times twenty it's very similar to what our last one was alright so for example if they've selected the first value the first remember we've got we now know this is going to be one two three or four based on this formula right so let's say it's one if it's one minus one is zero times 20 is zero that means our first row is 65 however what if selected is - what if our selects OPSEU 2 minus 1 is 1 times 20 is 20 so 20 plus 65 that means our first row is going to be 85 all right let's do a third option if they've selected the third option in the menu 3 minus 1 is 2 2 times 20 is 40 40 plus 65 so our first row is going to be 105 right the first 105 is going to be our first row of our third option so basically what we're doing is we're converting regardless of what they've selected them all right we're already running the code doesn't mean to do that yet okay so let's we're not quite finished yet we've hidden everything you see the code ran we've hidden everything but we now we need to show it so let's do that let's run our code so we've got our first row dot rain's let's do our how do we going to have to now show it our first row we know our first row first row and quotation mark colon quotation marks and our last our first row plus 19 all right there we go now we've got an entire row hidden equals false we're going to show that we want to show that and of course we have to set up b3 for our conditional formatting remember b3 is going to tell us for our conditional formatting we need to put that row in there so let's do that dot range b3 value equals equals our selected row plus our first row we know our let's say I select two rows of 1 our first row so that will give us our first row in the selected rows 1 our first row might be 65 so it's gonna be 66 if our Select so this puts the row number back into b3 so that's how that works and let's again let's just select another cuz I don't want the current dot range f2 and then select that way I don't want I don't want the existing cells linked so that's gonna select it alright let's take a look at that let's unhide this because the code hit it unhide it go into payroll detail right we don't need this anymore and let's click on earnings detail nice okay we don't know let's just earning it's hard to see earnings detail we put the text in there tax information tax just for tempura tax info paid deductions it's looking good and pay Edition so I'm just marking them just so we know just we can see looks like everything's hidden so now earnings detail tax information pay deductions all separate perfect we're good now we've got art now we've got our vertical tabs we've got our horizontal tabs very nice I think we have to do a little bit more work here okay let's take a look at this we've got our vertical does but our payroll detail let's go back and show that payroll detail we have to hide more we have to hide more we ended this it we ended this at 1 105 before so look we're showing this we can't show that we need to hide more in other words now our our calls extend all the way to 144 so when we hid these tabs we need to increase that let's do that in the code because now we need to increase our hiding so before 84 was enough right but now we've added vertical tab so let's go ahead and 144 let's change that to 144 we need to hide more now always need to hide more okay so we're good with that now when we click the horizontal tabs we can hide that but look we're missing the bottom row again again so let's take a look at that because last time it was 84 85 now it's 144 so let's fix that again let's take a look at that format the cells again same issue but different cells now it's 144 145 unhide it hide it show it perfect there we go that's it alright now we're good now we're showing that bottom row good good good all right now we've got our vertical tabs we've got our horizontal tabs everything looks just right I don't think there's any issue for right now and then good when we select option nothing happens that's what we want select option nothing happens any blank wrote nothing happens only if there's tax and only if it doesn't so that's working great let's add in some information on our payroll detail where we're extending this training along a little bit today I'll also want to increase this I want to give more space to that employee name merge and center and then over to the left and all right let's set up our borders there we can get so that way we can show a nice large employee name and have plenty of space when we do this is going to be a drop down list in future we're gonna drop down list and as we select an employee all the information is gonna show up here that's what I have planned but of course my plans can change based on your comments and suggestions so if you're still watching this and you're still awake please comment below share and like it I want to know what you want to see in your employee manager so we can try to get in as many recommendations as possible we've got a lot of blank space we've got a lot of ability to add and remove we could even add more tabs so there's a with this the ability and this application there's a lot of room we have a lot of space we're gonna add a picture in here although I think we're going to probably do that next time because we're running out of time but let's say go ahead and add in some information into the earnings detail so that we at least we can get that filled out too with some idea so we'll start out with pay type because this might be hourly they might be hourly or salary so we want to put in a pay type and if it's salary you want to add in a frequency it could be yearly weekly salary frequency I'm just but these are gonna be spacers and then we'll skip around go to hourly amount but I don't want hourly amount always right I wanna I want it could be salary what if what if they've this is gonna be our lis it could be salary so we're gonna this label is gonna change based on this it should at least so let's continue on with our labels and then we'll add overtime amount because there might be an overtime rate so we want to add that in next up we might want to put in something like hours per week and then weeks per year that's okay for now we're gonna add probably add a lot more to this in the future let's set our fields and we've already pre go into home we've already set our cell style so we know that's label and then of course we're gonna change the border that thick borders gonna alright that looks good alright and our fields here we have our field set so let's go ahead and set that to our field cell styles feel good that way we have consistency tax information let's put in some tax information here just a good idea it's different in every country but we can start off with number of dependents kids or family your members that you pay on taxes dependents I think I spelled that right probably not state or territory and we can remove that we don't need that now and then maybe social security number SSN social security number or ID number sometimes we have like ID numbers in certain countries and then we can put in they'll leave this blank for now I don't know what we're gonna put in there I also want a table I want to show like certain federal state local taxes so let's put in a table I'm gonna merge the center this and then I'm gonna create a table I want to create below that I'm gonna put let's say tax name enter tax name because there's different types of taxes for every country and wage percentage that's the percentage of the wage that it's gonna be taken out of and base unit would be that our maximum or base limit would be the maximum so if there's a maximum amount that you can earn per year and then they don't tax you any more we want to put that in some taxes how that and then maybe a maximum cost we just calculated next I think something like that let's format those we'll Center that and then let's give it a format so we can format our tables consistently Phil is going to be our medium Phil which is going to be here to light and then we're going to use a little bit of darker fade on the above one so we're gonna call this let's I don't want to merge the center all the way just the just the four columns merge the center here and then let's give that a name we'll call it federal you can change it federal and local tax because we need the tax deduction deduction all right good bold that control-b and then format those cells and then we'll give it a font well that's okay for now Phil we're gonna fill it which is gonna be a little bit darker of a fill so we give a face so we're going to use our medium to medium light here which is just that way it'll blend better in here we can give it a border here format those cells and we'll give it a little bit thicker border on the left and it's give me the top and the right inside the borders will will give it the format those cells will give it a basic line here all right good now let's go ahead and inside format the inside cells using the same consistency the dotted line for the center and we can do the top and we're going to fill it with white and then use conditional formatting to use that and here's a little trick I'll show you we're gonna use a formula you know the mod formula already so we've been over that and then what I want to do is I want to format the same color we colored before the same fill color but when we when we don't have the custom field remember we don't have a custom fill colors we use the color before but it's not here but it is here when we go to fill effects it's here that custom color right here so if we use the same custom color that we did before and we click the same if we use the two colors but we use it the same color that way we can use our custom color so that's the same color we used before and there we go that looks nice okay so let's put this as our field we'll add one here even though we don't know what it's going to be yet and give it that our field style and then also here is going to be our label so that way we can quickly create these and our field label alright so we don't know what this one's going to be yet but we got we got an idea of that and I want to also add in paid deductions pay deductions here so we're going to copy the current table and I'm going to add some paid deduction is here there we go so let's add those in and I'm going to do the same thing for pay additions and paste that table in here now we can customize it quicker so we can do payroll I did deductions and let's say the deduction name because we want to give it this this we can do so they can deduct certain deductions on each paycheck duction name and amount what is the amount and also frequency you know could be weekly monthly bi-weekly so we should set a frequency on here and also we want to know the annual total it's easier when we annual the total so let's say you have a frequency deduction of monthly but your payroll is you know weekly it's easy once you have the annual total we can then create that payroll check per check deduction not based on this amount not based on this amount but based on this amount the annual total will do the same thing for pay additions so we have addition name we will have amount frequency and annual total all right that's that's good that looks good and I'll make those both control be paid deduction so I'll make this bold control B and tax information also consistency both all right that looks good now we've got our we got a really good base a really good base of an application that we can really build on I think we're gonna leave it here because I really want to get your opinion I want to know how much we're gonna add in the picture will go here way out and we have more space for employees we're gonna add an employee so there's a lot of space there's a lot to build on this but now you've seen how to do horizontal and vertical tabs very quickly very easily without using shapes so I'm glad to show this to you I'm really looking forward to part two we're gonna continue to build this out based on your comments and suggestions so please share this like this and looking forward to bringing you next week thank you very much for joining me [Music]

Channel: Excel For Freelancers

Views: 171,137

Rating: 4.9178486 out of 5

Keywords: Excel Tabs, Tabs in Excel, Excel Horizontal Tabs, Excel Vertical Tabs, Add Tabs To Excel, Create Tabs in Excel, Excel Employee Manager, Employee Manager in Excel, Create Excel Employee Manager, Separate Data with Tabs in Excel, Excel Sheet Tabs, Create Horizontal Tabs in Excel, Manage Employees wiht Excel, Excel Employee Manager Part 1

Id: iyr9WIyxlJc

Length: 61min 40sec (3700 seconds)

Published: Tue Sep 11 2018