Ask only and it's very simple first go ahead and click that like button that really helps and comment below i love to hear your ideas on the future training or feedback on this training this training and application is completely free all you need to do is click on the link down in the description below either with your facebook or email and we'll get that sent over to you right away however if you do like to support us one of the great ways to do that is with our 200 workbook zip pack that's just 77 it comes complete with a library so you can get a single click to open the.
Workbook or single click to view the youtube video that is a really popular sale i love creating these applications for you because i want to show you how you can make your own money using selling these applications and what i've done is i've put an incredible mentorship course together and now it is complete that means you can now access all 132 hours of my mentorship course all for a single price we also have a great promotion running now i'll include the link down below you can get to it.
Myexcelmentor.com that's myexcelmentor.com and that's going to show you how to define design develop and deploy your own excel applications for passive income all right let's get started i've got a lot to cover on this training we're going to go over the details of this but basically it's a relatively simple there's one or two macros that we're going to go over it's not going to be a very long training it'll be one of the shorter ones it's probably still going to be around an hour don't hold me to that let's see how long it goes but i want to move slowly and show you exactly how this is created okay so but what we've done is we're starting out with a staff list so we're going to start that out and we have a staff id that includes a staff name and we want.
A supervisor and we want to make special note those that don't have a supervisor or the one that doesn't would be usually the co president we also have a signed position of that president our chief financial cfos and then we have different positions we also have underlings now this is going to be done with a formula underlings is how many people or staff are under them for example fred has three people under him we're going to create this from scratch and i've got this is the sample one so fred has three underlings mary dave and peter so while mary has also three other links lisa greg perkins and anita here and david has two and then we have larry greg has one under him at.
Say something some picture is spelled wrong or you don't have a picture i do want a default picture so if we run that organizational chart and notice i've changed david pictures so we've got a default picture which is kind of nice that we're setting up in case we've had a let's say there's an incorrect spelling or something like that or we just don't have the pictures we can at least have that very very helpful having those default pictures and also want to know the row that this is situated on so that's all the data we need that's it that's all we need row three four this is corresponding to the row here all right so the first thing what i want to know is i want to know the underlings we call them underlings or staff that's under them fred has three just as i explained to you one two three right so how do we do that all we need to do is.
Count use a count if if it's empty we're just going to make it empty if b3 is empty it's empty otherwise we're going to use the simple counted formula we're going to count i've got a named range called staff supervisor that's this in column c we'll go over that named range in just a moment and now all we're going to do is i want to know how many times fred was used as a supervisor notice fred is three times and we're gonna do that that's three so that's how we're going to get the three let's go over the few the name range not very much just a few of them here and i'll bring it over here so we've got some criteria and extractions we'll go over that's for the unique advanced filters there then i have a staff id that's going to be an offset formula based on the stuff i don't even know if we're going to be using that i've got a staff name here based on the main staff name right that's all the staff names we're using offset to create that.
So that it's dynamics that has our list increased so does that named range and then lastly we have a staff supervisor that is it relatively simple for this so i just want to keep track of those names staff supervisor so staff name so that's pretty much it so level this particular one is not generated by a formula it's generated by vba i want to know the level so if i delete that and rerun it it's automatically this is what i like so we don't necessarily need to know the level of it but i've run that organizational chart it's going to automatically come back here through.
Vba because vba is going to tell us what level they're on these are all in level two dave peter and mary are on level two if we know goodario mary dave and peter are all in level two and then we have five of them on level three and two on level four two on level four and the rest on level three so vba is going to take care of that because we need to know what level as we place these images all right then we have a picture now the pictures are located in a folder and to do that we've got an admin section and the admin section is going to attain just a little bit of basic information.
The first thing i want to know is what folder are those tags so make sure when you get that if you decide to put in some pictures have a folder that says our folder does contain those pictures here so we see in this folder here i've got the picture so located along with the default picture so this is the default picture called default picture it is that default picture that i've assigned right here so there's the entire length i didn't use the browser just kind of basic you can add a browse button here if you want but that's incidental then what i want to do is i want to have a style i want to be able to choose the style this is a very simple macro we're going to go over all i did was just create a macro and then place the check marks and if we notice here located right inside.
Posts Related:
- Design A Responsive Homepage With Full Screen Video Background - Part 3 BlogSpot
- Learn How To Load Employees & Pictures with this Excel Employee Manager Part 2
- Microsoft Teams Learn How to Use Microsoft Teams
F5 style changes accordingly okay so if it's style one style two style three so if i select
Style two and then i run an organizational chart it's automatically going to be that style that has changed so let's go into the macros for just a minute so i just wanted to bring a few things out and what we're going to do is i'm going to create these macros with you but i've already done so i've got a sample here that we're not going to use but we're going to use it for references as we're because as i am creating this i want to refer back to it so we'll use this sample so i've got two workbooks and here's the other workbook this is the original there's nothing here right so i haven't created any if i click this button nothing's going to happen we're going to create.This macro live but i've got this workbook so we'll switch back and forth and i'll keep you motivated because i do want to refer to that so again let's just do a few things one we've got different levels here level one level two level three and level four there's another thing that i wanted to note we want to control the spacing right we want to make sure we control the spacing just keeping notice that if there's a certain amount of space between this group right here look under mary mary's got three underlings three staff that that look up to her follow her and we want to make sure then we got a little bit of additional spacing here than mark and jack so keep that in mind as well so they're gonna be different we'll go over that but just keep in mind i've separated the spacing just a little bit on those that are not under it so they can do that all right notice.
Peter doesn't have anything under him also notice that if you make a change so let's just take a quick look under the staff let's say we decide larry's now going to work for anita so we just put in larry and let's say we want him to work for anita it's going to automatically change so now we change that organizational and we can see now that anita is now placed there and also need us so now node automatically changes and updates okay just so we understand that that okay very good so let's get into some of the macros that the first one macro i want to go over with is this one we'll just go over real quick this is a few lines of a macro i'm going to go into the developers alt f11 we'll get you there we'll go into the visual basic okay so let's take a look let's i've got two workbooks open making sure that this is the sample that includes all the vba code that that's.
Working and then we've got um organizational chart macros we're going to be writing just two macros today one called organizational levels that's going to set our levels and we've got another one that's going to actually create the chart okay so those are the two macros that we're going to be creating there's one i wanted to go over with you and that was this one here select style see how what a little bit of code it is it's very simple that is the macro that's been that runs this so how do we know well the first thing we want to do is when we create something like this and we want to change the style with it is i want to really focus on the shape name so i'm going to open the selection pane here we take a look at some of the selection panes take a look at the style one group.
This is the group that's associated notice that every single shape inside the group let's bring it out a little bit more here notice that every single shape is called style one if i name every single shape and same with style two the group the groups don't matter in other words the group header group can have any name itself because there's different sub groups but every shape style two style two every shape inside that contains the word regardless of the shape if i select on anything inside here whatever that shape name is i'm going to place that shape name right here whatever the shape name is i'm going to so notice that i've specifically spent a little extra time.
Naming those shapes very very specifically that is the exact style that i want to assign inside f5 so once we know that once we do that work and we assign those names it's very easy so again with the admin sheet what we're going to do is i'm going to take the name of the shape this the application call remember if you try to run this macro it's going to create a bug application call is always going to create a bug if you try to run it from the vba that type of macro must.
Be run from when clicking a shape why because it's looking for the shape name name of shape okay that is the name of the shape they called it but if you're running this macro directly from this play button here nothing's going to happen okay so keep that in mind so i'm going to take the name of that shape whatever selected i'm going to place that in f5 the next thing i want to do is i want to move this checkbox i've got a check box a little check mark i should say this check mark's called check mark obviously and all i want to do is i want to place this according to wherever we selected so basically i want to position that check mark based on the shape that's selected right that shape here it's called style for group style for group okay so i want to place it there.
So all we need to do is with the check mark we're going to place it to the left of the application caller style 4 and the word group left 90. or the top is going to be the admin remember we have to call out that sheet again because i'm inside the shapes here admin dot shapes application caller right in this case style 1 style 2 style 3 and the word group because what i want to do is i want to call out that entire group and notice the names of these groups they're all very specific so notice the space here style one so this is the shape inside and then all we do is add the.
Word group onto it so now i'm placing this check mark based on the selected style so that gets there so that's very very easy just a few lines of code i'm going to place the left position basically to the you know the left position of this group plus 90. so if it was just on the left position it would be right on the left part of the group so if i took away the 90 here and i decided to run it it would show up right on the left position but i'm going to add 90 because i want it to show up on the right side that's it that's all we have to do and then we're going to.
Place it a little bit less than the top position that's it that's all we have to do for the macro the most important thing is is to set this style here under f and that way it is that
Style that we looks up inside the organizational chart when we run it okay so keep that in mind all right so also inside organizational chart what i've done is i've created some samples here right this is these samples that we're going to get duplicated we've got sample one group and i've got a connector sample one now all of this is is a circle all it is is i've created a circle and.I've put a picture in here if we were to do that again right all i would do is just create a shape just create a circle here like that then what i'm going to do is just want to add a fill so we format that and all we do is shape fill we're going to add that picture so i select a picture and i want to select that specific picture that i have so in this case it is this picture here so that's all we've done here okay and then the rest is just a simple rectangle here and i've done it and i've given the name staff description so that's all i did here and then i've grouped them together so we're going to call this staff description this is the same regardless of the color staff description stat picture the only difference is the groups are named different.
This is called sample group one this is called sample group two three four and five and i've done the same thing with the connector this is an independent connector called sample connector one sample connector two sample connector three notice that the numbers correspond with the numbers here style three style four so that means that if i want to determine what group we're going to use all i need to do is remove when we've selected to remove the style in the space and it's going to.
Leave me with the number five if i know the number five i could have just put five here but it would have been you know a little bit more simple to put style five and then we understand exactly what's going on with that so now if i wanna refer to this group right based on this if i know we've selected style one and i want to get this sample here all i need to do is extract that one which we'll do in vba and then add the word sample group and then i've notated it and the same thing here all i need to do is extract that one and then just add the words sample connections so that's how we can differentiate between each one of these samples and it is that one that we're going to be able to generate the chart based on that so that's pretty much the overview of what we want to go over and.
How we want the style is and now what we're going to do is we're going to go inside vba we're going to start writing up the first macro that i want to focus on with you is the macro that's going to organize these levels now this macro is going to run every time we create a chart but i want to do i want to get those levels i want to know if we've got blank here i want to know that fred is on level one that these are on level two because if it changes right we need to update this if there's a staff that makes a change we need to automatically update these levels each and every time we run the organizational chart it is these levels that are going to help us write the macro to generate those pictures and generate that style and and also the connectors that connect them we.
Will also be using advanced filters and where we have some criteria that's been set up and then we have the result of those filters coming here we're going to go over that but we want to write in that code so let's get those levels it's a relatively easy macro so we're going to write that right now all right let's close this up here and i'm going to go into the organizational chart here we'll keep this sample open because we may need to refer to it that's the sample and but i want to go in here okay first thing we've got a lot of variables that we're going to cover we've got a leader a string i don't even know who the leader is the leader is the president or the ceo or he's the one leading everybody he's the top person i also want to know a supervisor as we uh know this i want to know who the supervisor is of a specific staff name is the actual staff the id of the staff the.
Superior id i want to know the id of the superior and the picture name as a string also we're going to need a picture file what is that picture file and a position what is their position are they president ceo or the cfo or whatever picture folder what is the folder location and i also need to know the default picture okay i want to know the staff rows we're going to loop through those staff rows the level is at one two three four five right the last row if we're going to be running our advanced filter row we need to know the last row of course that's going to be the last row of all the staff as we run our advanced filter through that so we're going to need to know that and so also what i want to do is i want to have the result row as we run through results and.
From our advanced filter in the last result row a previous quantity we'll be going over these i just wanted you to familiarize yourself i need to know the previous quantity that was coming what style is it style one through five we need to keep that as a long variable right that's gonna be foundstaffrow we're gonna do a found leader row we're gonna we're gonna do a find so we can find them we're gonna define the chart as a shape right we need to know those individual sharps the level quantity how many levels and the level number so we'll keep that in mind i'll go over that with you as we go and then also the left position on the center position right those are positions as we move this chart around so that's going to be double just like the top position and the shape width and the shape spacer and then the previous level so we need to basically get.
All that information here all right so let's get these levels in so i want to get to the last row in this macro then what i want to do is i want to first thing i wanna determine who is the president the president doesn't have a supervisor so that's gonna help us determine who the president or ceo is so that's the first thing we're gonna do inside this macro so let's do that right now we're gonna focus on pretty much one sheet for the most part so with staff is the sheet that is this that is the sheet that has all of our staff on it the first thing i want to do is determine the last row and that last row is going to be equal to the uh the last we'll use column a on that that's the last row of data we need to run an advanced filter so we need to get that last row if the last row is less than three then we can exit the sub okay so we know we don't need.
To go on if it's less than that first thing what i want to do is i want to find i guess as i mentioned you i want to find that row that has no supervisor i want to look i want to know who the top guy is who's your top boss right i want to know what row that's on and of course that's going to be that person that doesn't have a supervisor so that's going to be because we have to set our level one we know that is so we're going to do is going to find that we're just going to look inside that so we're going to do set our found leader range as equal to dot range and we're going to use the find so first thing we're gonna do is based on that staff and then underscore supervisor remember that is the named range set up for all the supervisors supervisor okay supervisor but what i want to do i want to set find what is fine i'm looking for in this case i'm looking for the empty space i'm going to look in excel values and i'm going to.
Look in the excel hole okay so we're looking for that looking for that first empty one locate let's put a node in here locate staff without supervisor if found is nothing let's let's let's write it out so that if it's not found we can exit the sub app there's nothing we can do if we don't have the top guy okay if found is nothing found in this case leader range is nothing nothing then exit the sub if no leader exit we gotta have that because we got to know the level one so if it's nothing right if it's found if if we're writing something we wanna know it's found we'd write if not found that means it is found but in this case we don't want it found okay if it's not found nothing okay now assuming that it is found we can continue on so we can do is dot first of all i want to set.
That leader that's in column f so dot range f we can set that row what is the row and found leader range dot row what is that value it's going to be equal to one equals to one set the third not twenty one set set first level okay good so we've set that first level we know who the leader is and we set it so what i want to do now is i want to run an advanced filter and i want to know in this case i want to know the second level how would i know the second level the second level would be anybody any person who in which fred is the supervisor so if i put in fred here and i read an.
Advanced filter it's going to tell me every every specific staff that has fred and so it's going to return these three these three that's what i want i want to run advanced filter so then what i do is i loop through those and add those to two so first thing i want to do is clear out anything that might be in l so let's do that right now so that range l3 through l and that's just 99 anything dot clear contents not clear contents let's get clear previous criteria so next up l3 is going to be equal to what it's going to be equal to fred equals okay and in this case whatever our staff.
Name is and our staff name is located on b so i'm just going to copy this i'm going to change it to b equals b here so there we go so now what we've done is we put that staff name or that supervisor name really in co in this case so that's going to go directly inside l3 so we're going to set that supervisor name for level one now we're ready to run our advanced filter so we can do that let's initially set the last rows three what i want to do is i want to set an initial last row because in this case i know the last criteria row is going to be three right so i'm going to run an advanced filter and i want that criteria between the l2 and l3 l2 and l3 however that three i want to put in a variable and that will come in handy in a minute so we're going to do is we're going to do the last result row equals three initially and it's going to change okay so the last result row now we're.
Ready to run our advanced filter right after we enter a loop so basically what i want to do is i want to loop through all of the levels starting with level two right starting with level two and go all the way let's say to five but you could go more if you've got a larger we'll just certify four level equals two two five because we're going to do this next level okay so now we're ready to run that advanced filter so now we're going to run that advanced filter okay so how do we do that filter is going to be based on this we're going to start at a2 all the way through h that is going to be our initial data so and then of course the last row a2 changing this to h we've already set.
Our last row here so we've got that our criteria is going to be a little bit dynamic so we're going to start it out in l and go to the dynamic field which is the last row and that'll come in handy and i'll show you why in just a moment so normally l2 and that's l2 all the way through l and instead of three what we're going to do is we're going to do this last results row and the last result row so it's gonna be dynamic in the first case the first time we run it it's always gonna be three but since we're looping we're gonna make a change to it in the next time okay where do we want those results to go well i want those results of that filter i want it to go inside p through w so.
I'm gonna change this to p and i'm going to change this to w that's where our results are going to go and right here so here's where we have it so the last row w then what i'm going to do is i also want to know how many underlings the supervisor if here's a supervisor i also want to know how many do but that's going to come in a little bit later so in this case all we're going to do is just get that results and put them in here later in the next macro we'll focus on this part here so we've done that here and now let's get that all the way in through w going to bring in those results there coming in there w unique equals true now we're going to set the last results row so now.
The last result row equals in this case we'll use uh p column p is going to set our last results row last result row equals dot p99 and excel upgrade so last result row okay so if the last result row is less than three then we need to exit the seller in this case we'll just need to go to the next one so but we can probably that means there's no underling so we can move on there's no staff below that but we can do something like this last result then let's say go to just in case go to no results so go to no results you could also do and and and we can then we'll go down here no.
Results okay that'll essentially exit out of the sub here so we can go actually go out of the loop here right there okay next level let's fix that no g in there so now we've got the last results row and now what we can do is continue on so we've got those results row so what do we want to do so let's say let's say the results come in here and we have three people right we have dave peter and mary those are the three elites for each one of those i want to set the two here but how do.
We know well we know the row that row is going to come directly in here so it's going to be row 3 or 4 5 and 6. so i'm going to take that row whatever the original row is and i'm going to update that level i'm going to put a 2 here i'm going to put a 2 here and i'll put it to here based on whatever rows here and put it in column f so that's the next line of code that we're going to write so we can do that with a loop because they're all going to be 2. so 4 the result row is going to equal 3 to the last result row and then next result row so what do we mean by that that means we're.
Going to start out at a 3. so 3 i'm going to go from 3 4 and 5 all the way to the last one determining whatever row the original data is in here and then updating that level column to whatever level we're on okay so that's all we have to do in this case it's going to be two but we're going to loop we're starting at two and we're going all the way to five so the result now we can do that so now we can just set that result row dot range f we know the column we want to place it and what is the results row where is that results well it's located in dot range in side w dot rain w.
And the result row and the result row dot oh it's already there dot value so that is going to take on equals the level set level level as we loop through that so all we're going to be doing this macro is just going to updating it so now what we've done we've gone through all three of these right we've determined the row we've updated it we've put a 2 here put a 2 here now we're ready to go on to 3. but how do we get 3 well what i want to do in this case is i want to take these staff people i want to put these staff just like this copy this basically and then paste the.
Values now what i want to do is i want a criteria i want to use and i don't want to run an advanced filter i want to know anywhere inside this table where these three are the supervisors right so for example marry this one here right any case i want to know that right so then mary would return now i have more results right so look there and also dave here and dave here so they would return all these results right that advanced filter so anywhere any one of these three names are used so we've got our results here right these three people came in here the results so now.
What i'm doing is i'm going to take these three names i'm going to copy them i'm going to place them right here these results now become our criteria as a supervisor so we can then get every time that they've been used so then i can return all of these notice i want to return all of these in an advanced filter and then it is all of these that are going to become level three we're going to do it last time and to do level four so that gives us level three and four inside our code so that's all we do we just have this loop right now so the first thing what we want to do is clear the contents of level 3. so again i want to run this here i'm going to run this and just clearing any contents now what i'm going to do is i'm going to place dot range.
L3 through l and the last results row last result row equals what equals basically i'm going to take whatever is located right in here q3 and the last results row so equals dot range q3 q3 and the last result row dot value okay what that's going to do is set next criteria now all we're doing is taking these names and bringing them down here in here that's going to bring them right down here and then we're going to run the advanced filter from that good i'm glad we got that all right let's take a look at that so we've got it that's all we need to do.
It's just going to run that loop so once we have that next criteria we run that advanced filter again so what we're going to do is we're going to clear out these levels we're going to let vba go take over and complete that for us so there we go let's take a look one two five threes and four perfect okay that's exactly what we want we've got our level set now we're ready to run our main macro that's going to run that the first thing what i want to do is i want to copy this and i want to make sure we run that every single time we create that chart so that's the first thing we're going to do first thing i also want to do is i also want to get that style we have that style as a long variable up here so i want to determine what style are we going to use one through five.
Style is going to be equal to okay we're going to use the replace in this one because remember i said i need to extract that number admin dot range where is that locator is located in f5 dot value that is the style remember that's the style that we've set right here style it's going to be located in f5 all i want to do is remove the words the text style and the space all i want to do is remove that because it's going to leave me with whatever number i want that's how we do it so i'm just going to do that inside that so we're going to use replace and what am i removing i'm removing what's called style and the space i'm replacing it with what nothing what's that going to leave us with that's going to leave us with that number that's it that's going to be the style number now that we have that we can use that to determine what.
Style we want to to use okay good so we're going to focus again with mostly on the organizational chart sheet that's a with organizational chart click the period to make sure that we've got the intellisense and we've got to write okay so with that first thing what i want to do is i want to remove any shapes that are associated with the organizational chart that might already be here there's none in here but i want to remove any if they are so to do that we're going to set some specific names when we create them so that we can easily remove them when we need to so if so we're gonna do for each chart shape we've already created that dimension that as a shape.
Chart shape and of course dot shapes right what do we want to do so we're gonna close our loop next chart shape so it's just one line of code if in string we're checking for something chart shape dot name i don't want to delete every name in the in the of course in the sheet only those with specific criteria and what exact criteria those names that are have the text chart item chart item making sure your samples here don't contain the word sharp item you know they contain sample groups so there we don't want to delete these so we want to make sure the names don't have those names associated so we're going to delete everything that's called.
