Learn How To Create Tabs in Microsoft Excel In This Easy VBA Tutorial Video

hello this is Randy with Excel for freelancers and  today we're going to show you this amazing trick   of using horizontal tabs within an Excel worksheet  and it's a great way to display lots of data in   a small area all righty let's get started I've  got my horizontal tabs worksheet open here and   as you can see it's been divided up into three  sections I've got general employee information   I've got some time clocking history detail and  I've got an earnings history table so I've got   these three sections and basically the idea is  what we want to do for the tabs is to create a   tabs that display the sections individually all  right so here's how we're going to do that what   we'll do is we first point in add the shapes  and to do that we'll go into insert and then   shapes and then we'll go ahead and choose our  same side corner rectangle here and we'll go   ahead and just put that out here that should be a  pretty good-sized bear we'll make it a little bit   longer and so to do that now basically we all  want to do is duplicate that five other times   so we're going to make six of these three tabs  six shapes so control D one two three four five   and that creates our six individual tabs okay  next what we want to do is we want to grab those   and differentiate them into two different sets of  three and to do this I'm going to use this select   objects tool here if you don't have this tool here  in your quick you know I recommend you get it when   working with sheets and to do that all we have to  do is go into more commands and then what we'll do   is we select the all commands here and we locate  under and we locate we're looking for something   we're looking for this select objects because  it's a really anti tool and then once you locate   it you'll just add it click it and add it over to  that since I already have it it's not going to be   there but that's a great way to get that tool and  to - this tools get out once we click that tool   we can then go ahead and select so we're going to  select three of them okay and we're going to move   those over going to separate those and then we  get we've got three so so that's a great way to   separate and then just make sure you unclick the  select objects tool when you want to go back to   working in our mode okay so the first thing you  want to do is want to diff we've differentiated   our three different tabs and we will select  another way to select multiples hold the ctrl   down and then select so we've got three of these  now we want to change the look of three of these   tabs and to do that we can go here to format 11 or  we can right-click and format the objects and one   it seems the field color and what I want to do is  I want to change it to the background color a part   of it so we'll go to a gradient fill here okay  linear is good and then we're going to go from   light to dark okay which is good it's actually  already set up on mine you might have three three   taps so if you have an extra one here you know  that you look at it just click there and then   remove it so basically you want a two different  gradient stops okay so our dark stop we're going   to go from light to dark so our darker stop will  be this color here and use this because this is   the same as a background color and then our second  stop here will be we'll go with a lighter color so   we're gonna go from light to dark okay so that we  have this this looks okay and then close that okay   so now you see it's got the light to darks and the  idea is for the dark part to match the background   okay and the border looks a little bit thick  on these so let's change the border but I want   to change them all so I'm going to go back into  our select objects and I'm going to I'm going to   choose select all of them and then what we'll do  is we're going to change the border to a little   bit smaller border so in to format shape outline  and then we'll take the way down to perhaps about   one point that would be good okay and now we've  now let's go ahead and change the look of these   bowls surround those and we'll go into format  and maybe we will let's give it up flat dark blue   so now we've got three different tabs in three  different in two different sets three tabs each   okay now let's uh go ahead and insert a border so  that we can encompass all of those so we're going   to basically take this shape which is our rounded  rectangle we're just going to bomb let's see give   it a nice border here okay and obviously we don't  want to fill on that we're just looking for a   border so we'll click no fill on that okay we're  going to make this also the same thickness on the   weight so we're going to also go to one point here  okay and we can lower that a little bit change the   change the look of it okay so now our tab is  starting to come into okay and the important   thing is the idea is we want to move we want to  basically hide columns and show columns based on   you know the click of the tab but we don't want to  do is we don't want to move any of these shapes so   once again back on the select objects and we're  going to take all of our shapes okay all of our   chips and then we're going to right click and go  into size of properties and basically we want to   say hey we we want to we don't want to move or  we don't want to size any of these shapes we   want them when we change the columns you wanted  shapes to stay the same spot and we don't want   them to change the size so that's important don't  move or size ourselves okay so now we've got that   and now let's go ahead and place our shapes our  tabs won't smooth them over a little bit so that   we can get a good eye on them okay for now so we  can differentiate them so we'll you basically the   the light of one's going to be for when it's on  and the darker one's going to be for when it's   off okay so so let's go ahead and give it a name  so the lighter one will call this general info   okay and we also want to obviously it's we don't  want it in white so on this one let's go ahead and   make this font perhaps a will go up to maybe  14 we will change it to let's say Cambria and   we'll give it a color of let's say a blue this  is our standard blue and we can go bold and we   want to make that in the center so basically what  I've done is I've changed that I've centered the   text I've changed it to dark blue and I've given  it a different font Cambria and brought it up to   14 okay and we want to also these we're going to  go to a white font so there's something and then   also Cambria also 14 and also bold okay except  this one's going to be white so that we clearly   see the difference okay so we've got general info  they don't move that over so you can see it and   then our next is our time clock history so we'll  call that time clock okay and our third panel is   going to be earnings history so we'll just call  it earnings okay and basically we want to do the   same thing for the off tabs so general info okay  time clock and also earnings okay great and I just   want to also send to these like I've done before  with the others so we'll go ahead and center that   okay now the look is starting to come together and  now what we're going to do is let's just place all   of them horizontally even so we can see a line on  other bottoms and we'll place them and the idea is   we want to get it just right basically even with  that line okay so now if you alright so now let's   go ahead and do that over and bring this over here  so that everything lines up nicely okay great and   now the idea is we can start to assign macros to  these and the idea is when we're going to click   this shape we want this to show up when we click  timeclock we want this table to show up you know   and the same things for earnings here all right  so to do that we would use macros to that but   let's go ahead and place place them properly so  we want general info we want these to be directly   on top of each other okay so we want general info  and all also aligned to the left okay so they're   actually directly on top of each other same thing  with timeclock also aligned to the left and then   earnings also so that all the shapes are on top  of each other go ahead and click that all right   so now they're directly on top of each other and  a great way to see what you've selected is under   page layout selection pane this is helpful this  shows that we've got two selected so if we wanted   to select all we could show all or hide all and so  this means they're all viewable so that's really   helpful if you want to select a specific shape you  can do that here close that now okay so now we've   got we've got our cabs all set up and correct now  what we can do sir and go into the developers tab   here if you don't have that once again go into  options and you want to go into customize ribbon   and make sure you selected the developers tab also  you can get to it by clicking alt f11 alt f11 and   that's going to get us into the developers tab  okay and now we don't have any macros or anything   running or nothing going so we're going to insert  right click insert module okay now that we've got   our module created let's go ahead and assign a  name to that module because in some work much   you're gonna have a lot of modules so you want to  name them differently and you can do that simply   by clicking on the properties window here and  let's go ahead and give it a name we'll just call   it tab max if any name you want it doesn't matter  okay and now what we want to do is basically one   that we want to differentiate those shapes or  tabs that we've created by signing names to them   so we'll go back into our workbook now and once  again we want to click on the selection pane here   so we can see what shapes are visible and what  we're going to do is we want to temporarily hide   the off buttons which are here is holding down the  ctrl button we're going to hide those temporarily   so we can name the on buttons and we call those  on because that is the button that's going to be   displayed when we click the tab so we're going to  give each one an independent and unique name to do   that we can select the shape we click up here into  the day box and we can type in djenne and then on   if that's going to be on and then we'll go ahead  and click on the time and we'll give that a name   TI m 1 and also earnings will do var on ok so  now that we've named the three on ones and you   can see them here we can go ahead and hide those  ok we'll hide those and let's go ahead and show   the ones are going to be off which are here ok so  let's go ahead and name these and we'll call this   G and off ok hit enter and then the next one t IM  off and then EI are cloth okay so now we've got   six different buttons each with different names  ok great now this is going to be very helpful when   we go into the visual basic editor and we start  turning these on and off ok so we can go ahead   and hide the shapes now and we can go back into  our visual basic editor and we can start creating   these subs ok we'll create our first one remember  want to click double click modules and we'll just   call this sub tab Gen okay and then we don't need  to put anything and then we're going to create   another one sub tab okay and now we can call that  let's see we'll call that time and then we'll call   the other ones earnings okay time and then sub tab  times okay so we have three different sub menus   each with different names which is important okay  and since we're going to be working with sheet   one and each one and only sheet one it's better  to use a whisk so we don't have to keep typing   sheet 1 inside every line of code so we're going  to go with with sheep 1 and I have some automated   software that automatically types and with but  you'll want to make sure n with is there okay and   we want the same for each of the sub so we'll just  copy and paste that and paste that okay so now we   have sheet 1 now we can start each line of code  with dot because we've already designated which   sheet we're going to be using and what we want  to do is shapes we want to turn shapes off and   on dot shapes and then with the tab in general  info tab what we want to do is we want to win it   when click we want to display the on button and  hide the off button so Jen on okay this one we   want to display dot visible equals MSO true okay  that means we want to display the on button when   it's clicked but we don't want to display the  off button right so dot shapes Jen off okay dot   visible for instance they're not visible equals  and uh so false okay so that's the idea false   okay because we don't want to splay alpha and for  the other two it's just the opposite okay so let's   go and copy and paste those okay and we'll leave  a little space now we also have time ok time on   but for time on we don't want to show the time we  want to show it false right so once again if we go   back remember this time we this is the off we want  to display this off and we want to display their   knees up but we don't want to show them on okay so  that's what we're going to do so that's going to   be false and this is going to be true okay so  time off is going to be true okay and I think   it was t I am sorry I am I'm off we just used and  so the same thing for our last button that if you   remember correctly our last button was earnings ei  are often ei are on okay so we want to do the same   thing there EA are on is going to be false e a off  is going to be true okay so that's it and now the   other thing is what we want to do is we want to  show certain columns and hide other columns when   when the tab general tabs click so which columns  of those will let's go back into our worksheet   and take a look now we're probably going to want  to show columns D through K okay D through K we   want to show okay but L through let's say a a we  don't want to show L through a a we don't want to   show D through K we want to display okay so we can  go ahead and put those in programming program Li   dot range okay once again D through through K okay  entire column hidden equals false right we want to   show that right dot range L through what was it a  a right I believe entire column hidden equals true   okay let's double check okay that so D through  K displayed L threw a a hidden perfect okay so   let's go ahead and run that and see what happens  okay and running this code will run that code okay   it's been run although you didn't see it okay and  it looks right we'll go ahead and minimize that so   that you can see that code there and that looks  right it looks like be through okay I'll hit are   displayed and then the other ones are hidden so  that's just right okay so perfect that's exactly   the way we want it and you see that the general is  been on the on button is displayed and the off art   so it's perfect it's just the way we want it now  all we have to do is simply repeat that process   for the other two tabs okay and we can do that  by simply copy and pasting it here okay copy and   paste the code and then making changes but in this  case right in this case the on for the timer we're   going to be off false right we don't want to on  play and so basically we're just going to go with   the opposites on each one of these okay so we're  going to change true to false false to true okay   and it's quite simple once you once you get that  and then okay and now for the columns what we're   going to do is the ones that we're going to show  okay let's go ahead and back into our workbook now   and we're going to manually unhide these so we  can take a look at them okay so now we want to   show L through s okay L to s we want to show okay  and back into the VBA editor we go and we want to   do is we want to select L through s as displayed  and then we're going to put in two sets here   that's hidden the first which is D through K as  well as T through through ay-ay-ay okay so these   are the columns that are going to be shown and  hidden okay and then we can go ahead and run that   and simply by clicking inside there and clicking  the green triangle which is the run sub and back   into our Excel and we'll take a look at that  it looks like time clock is displayed and then   general and earnings are off and the time clocks  plate so that's working just right and now all we   need to do that is for the remaining one let's go  ahead and go back into that and we'll go ahead and   go back into the third one and we'll go ahead  and copy and paste okay and then what we want   basically in this case the general is also going  to be off right illness and the time clock in this   case we want on we don't want to show the on but  we do want to show the off and and it's just going   to be the opposite is for the earnings as well  we want to show on for earnings because this is   the one and we want to hide the off put that C  in there and so now we're set all we need to do   is set the columns in this case t through a a is  going to be displayed and D through s is going   to be hidden D through S is going to be hidden so  we can clear that out okay and s so that's pretty   much it we can go ahead and click inside there and  run for this and we'll go ahead and take a look   back in our workbook to see that and it looks  like earnings great now all we need to do is   assign those macros to the buttons and there's a  good way to do that we can go into layout here and   selection pane okay and now basically we're going  to find the same macro to each one so earnest and   earnings okay we select both of these okay and  we're going to right click and we're going to   assign macro right and all we need to do is tap  click tab earning okay so basically we've assigned   the same macro to both earnings buttons okay we're  going to do the same thing for the time time there   we're going to right click assign macro okay and  this one's going to be the time okay and then   we'll do the same thing for the general info click  on both of those buttons both on and off right   click assign macro and general okay so now when we  click the buttons we're going to get some action   okay general info let's go ahead and slide that  over open that general little so when we click the   on nothing happens because it's already on time  o'clock earnings kind of clock okay it's working   great except we're getting up slash and that that  that happens and I'll show you how to avoid that   you stand a little flash on the right side we can  we can remove that simply by adding a few lines   of code and then what we can do here is we can go  up to c1 and let's take a look at that application   dot screen updating equals false we're going to we  don't want the screen to update while we're while   it's running through the code but once when we  turn it off we got to make sure we turn it back on   to at the end so application dot screen updating  equals true okay and we do the same thing for each   one of those so we're going to copy that line of  code enter and then into this and then we're going   to turn it back on we want to copy that line of  code paste that down there and paste that at the   end as well okay so now we've got that now that  will help with our we get rid of the flash so   now when we click there you see that flash is no  longer there and we've got really smooth click   smooth tap entering and that's pretty much how you  do it it's quite simple you might want to hide the   headings there because it gives a nice appearance  when you hide the headings and that's how you   create tabs I definitely appreciate you taking  the time to watch this a little bit longer video   but it's a great way and a great tool to show lots  of data in a very small area thank you very much   for watching please don't forget to subscribe  to our YouTube channel and select these send   me all notifications options so you can get our  videos to you right away as soon as we create them

Channel: Excel For Freelancers

Views: 667,218

Keywords: Microsoft Excel Tabs, Create Tabs In Excel, Adding Tabs in Excel, Excel Tabs, Excel Tabs Free Training, Horizontal Tabs in Excel, Create Tabbing in Excel, Horiz Tabs in Excel, Excel Horiz Tabs, Amazing Excel Tabs, Beautiful Excel Design, Design Tabs in Excel, Excel Amazing Tabs Design, Horizontal Tabbing in Excel, Excel Tabs Design

Id: qxVl2vjWRUw

Length: 24min 14sec (1454 seconds)

Published: Thu Aug 03 2017