How To Make An Automated 1-Click Mind Map & Task Manager In Excel [FREE Download]

hello this is randy with excel for  freelancers and welcome to the mind   map task manager in this week's episode  i'm going to show you how to create this   incredible mind mapping application complete  with projects tasks and fully updateable tasks   all we need to do is just make a  few changes and then click save   and then that task will be automatically updated  within the mind map on just a click we're going   to show you that filters and a whole lot more i  hope you'll stick with us so let's get started alright thanks so much for joining media i am  really excited to bring you the mind mapping tax   manager i've had many requests for a mind map and  so today combined with the task manager i thought   it would be a perfect opportunity to bring this  to you in excel like we do each and every week   of course all i ask is just a few things if you  have not subscribed yet please do that go ahead   and click the links down below and either with  your facebook messenger or with your email we're   gonna get that sent over to you absolutely free  all you need to do is just a few things don't   forget to click that subscribe button and hit that  notification icon bell that is going to ensure   that you get these trainings each and every week  sent to you notified to you which is going to help   us out a lot also if you are new to vba we do a  lot of vba work and i've got a few courses on vba   to help but nothing like the ultimate excel vba  course by my friend daniel strong daniel brings   you everything from the beginning all the way  up to expert level in vba so if these trainings   are a little bit advanced for you daniel's course  is an amazing course i hope they pick that up i've   got a special discount he's been offering excel  for freelancers followers so i'll make sure to add   that link below go ahead and click the link and  if you're new to vba that's going to get you set   up to where you can do these applications and a  whole lot more very effortlessly when you join his   course so go ahead and click that link all right  great so i've got a really great mind map task   manager for you today and basically we have here  we've got projects where we can create additional   projects we have staff each staff can be assigned  a project and then each project you can have   multiple tasks assigned to it right the colors  denote the status of the task right so notice   we've got different colors those colors are fully  customizable here inside our admin screen where we   have tasks we have to do we've assigned that to a  yellow color in progress green overdue in red and   completed in blue so that way you'll see certain  different tasks have different colors right and   so you can see we know that when we click on this  site scope we're going to see that that is the to   do if we click here on the client meeting we're  going to show that that is in progress if we   were to change that to overdue and save that that  is automatically going to be updated right here   to that red color so that's going to denote there  inside the admin and of course completed we have   in blue we also have icons right our staff we  have an icon for staff we have an icon for project   one for tasks and one for view the tasks now as  you as an end user you can update those or use a   developer i'm going to show you how to  automatically add those icons now if we were   to add a staff we would see the icon if we had no  picture for the staff we'd see the see the staff   picture right so we also have a folder here this  folder is going to know where our staff pictures   are located i've got a folder right here and this  is where our staff pictures are located if i were   to make a change or small change to that staff  folder here let's say we have an incorrect path   and i run this and i refresh it we're going to see  that we don't have a correct connection to those   staff pictures right so it's not going to work  but what we're going to do is we can use an icon   instead which is kind of nice so that is where you  see this staff icon come in here and i'm going to   show you how we do that if i make the corrif i  use the correct path there making a small change   refreshing that you're going to see that it's  automatically refreshed we now see the picture   show up right fred is assigned to a certain amount  of projects right we may want to see all staff all   projects all statuses or maybe we only want to  show fred in to do right so if i were to click   click here and then show james new house or  show particular so it's only showing here it's   very specific projects that are only to do right  so if we want to know only those are completed   or maybe those are only to do we would then  show that we can only show so when we refresh   that james house we see that these two tasks are  overdue based on a mind map right and of course   if we are going to show all statuses when we go  ahead and refresh that it's going to then show   all statuses so i'm going to show you how to do  that and also we have dates right so notice we   have a start from date and we have a due date from  date so we can show it only tasks that are built   based on those dates so each task has different  we have a project where we can select we can also   add a new project dynamically so if we were to  enter a brand new project here that's going to   automatically enter i'll show you how to do that  we've got staff here if i decide i want to create   a brand new staff so let's say i want to create  sally smith and sally smith is no longer is not   a staff and i save that it's going to say hey sal  this staff has not previously been added would you   like to add it now yes of course we don't have  a picture for that but that's okay if i were   to then refresh it we're going to then show that  sally smith here now is located of course there's   no picture attached so we can dynamically add  that and we added this client meeting to this   brand new staff here so it's extremely dynamic and  very very powerful if we are to remove or reduce   the projects and the task it's going to show up so  there's a really there's a lot of ways that we can   dynamically make changes to this and i'm going to  show you every step of the way every line of code   every format and how we created this so also  inside our database we've got a list of tasks   so all the tasks are located here each task has an  id a task name and a staff id and a staff assigned   to that task a status as you saw start date and  due date a project id and a project name and then   a description as you saw and then we have a list  of projects here we also have a list of staff   here so we noticed that sally smith got added if  we of course add a picture for sally smith that   would then show up right so let's say we do have  a picture in that folder for sally smith let's   say let's use this mary because i don't have  lisa let's say lisa.jpg right if i add lisa.jpg   and i know that the path is correct and we don't  necessarily need an email and if we go back to   the mind map and i refresh that automatically that  picture is going to show up we now have a picture   for sally smith here because that's mapped  properly so all we need to do is just ensure   that one the picture name is correct and we also  need to make sure that our file path that we've   browsed for is the correct folder and of course  if you want all these icons and all the pictures   that i use that is going to be available inside  our patreon platform patreon is a great platform   where i do lots and lots more way beyond the  initial training in youtube so if you'd like to   get on that and you want to add additionally you  want features added you want me to fix something   or focus on something i'm doing all that inside  the patreon platform along with pdf downloads   early advanced videos downloadable training videos  pdf downloads discounts and a whole lot more so   i hope you'll join us there on patreon it's a  really great platform so we understand that staff   can be added dynamically and i guess eventually  maybe on our patreon we can add a picture here a   little button for a picture so we can actually map  that picture to the staff we could do that that's   something we can add up so as far as the task  it's relatively simple just some basic information   when i expand on the staff i want to know all the  projects located notice that the staff changed to   fred frederick's here right and i want to  know all of those projects that fred is on   and then as long as it's from the start date here  from the chat and then all statuses i want to know   the fred's remodel and then when i expand those i  want to make sure that those move up so let's go   ahead and reduce those now let's say i expand just  one project and we're going to show four different   types of tasks on that now if i expand another one  we're not going to have enough space so i need to   make sure that the spacing automatically changes  based on those tags and again if i expand this   fred's house remodel and there's three tasks  associated with that i need to make sure that   there's space so i'm going to show you how to do  that and of course i'm going to show you how to   use these connectors to connect both the projects  to the tasks and to connect both the staff to the   project so we're going to be showing you that  as well okay so you've got pretty much a round   view of everything that we're going to be doing  inside this and how that works and we're going to   go into it step by step and i'm going to take you  through every step so i hope you'll stick with us   this one i will show you everything and how we  did it and to keep in mind that we're going to   need something when we click refresh what that's  going to do it's going to clear all those out   and it's going to also let us know exactly how  many staff it's going to clear based on this and   all that is based on this data here inside the  tag so we're task we're looking for all the data   but the first thing what i want to know is when  i filter this data i want to know all of the top   staff that are scheduled on tasks between these  two dates right with a start date and i want to   make sure that the due date is going to be less  than this right and i want to make sure that it's   going to be from the due date due date from 7 30  and a start date from 501 so i want to make sure   it fits within those dates and to do that we're  going to run an advanced filter the first filter   that i want to run is i want to know all the staff  that are associated on tasks from those dates so   to do that we need to set up a filter and there's  going to be a filter based on these all of these   and we can do that directly inside the task so  if we scoot on over here we see that we have here   some criteria here we have mind map criteria now  these are all based on individual criteria that   we're setting up so if we take a look inside the  mind map and i decide i'm going to expand fred   we see that fred is now the staff that's been  selected if we go back into the tasks we see   that now fred is now the assigned staff so that  means that the results those unique staff right   or those unique projects are only going to come  inside here it's a unique project this is what   we want to focus on those unique projects are only  for fred notice these three projects these are all   unique results that came directly from here our  data and that's fred's house johnson so these   are these three projects have been assigned to  fred only and so those results are going to come   here so we're going to take it based on that  criteria so now what we need to do is we need to   develop criteria that is directly based on these  cells here kh3 for the staff j 3 for the projects   l 3 4 of the statuses and then 0 3 and r 3  for the start date from and due date from   and that's just what we've done in here so  what we're going to be doing is we need a   formula based on that and we need two rows of  criteria because we're dealing with dates here   so what we're going to be doing is the first thing  what i want to do is create a formula if the mind   map sheet h3 equals all staff then show empty  right i don't want to show any specific staff   for that or every a task associated however if it  is not empty i want to show that specific staff   that's been located i'm going to do that for two  consecutive rows for both o three and o four it's   going to be the same formula also if there's a  project name has been selected if i do select a   project let's say fred's house remodel i want  to make sure that the project selected is put   directly in j3 therefore only those particular  tasks that are dealing with fred's house remodel   will show up in the mind map will show up right  here and so that's what i want to show so again   inside our criteria we now see that the project  name is also it's a very similar formula except   we're dealing with j3 if j3 equals all projects  then we're going to show anything that's not blank   otherwise we are going to show whatever is in  j3 and it is that same formula in both p3 and   p4 we need two rows because we're dealing with  dates we're dealing with two different dates so   we want to make sure that we're going to show both  of those okay and again just like that if i filter   down a criteria as you can imagine maybe i only  want to show those that are let's say in progress   and if i bring this and and refresh it we're  only going to show those that are in progress   so inside the task as you guessed it the tasks is  going to show just that in progress and we can do   that again with this formula l3 if it equals  all statuses we're just going to show any task   that's not empty and then otherwise we're going  to show exactly whatever status that is in this   case it is in progress i also want to set up these  start dates and this is where the gets a little   bit tricky because we're going to show dates  and i want to show it less in this case right   if the mind map o3 i want to do is empty then i'm  just going to show everything greater than zero   right anything that contains a start date is what  we're going to show no specific date otherwise we   are going to show greater than or equal 03 right  i want the start date greater than or equal to   that specific date and notice the number right i  want i don't want to show that in a date format   that 4 4 6 8 2 is actually equal to this date on  may 1st right i want to make sure that that date   is in numerical form and that way regardless of  the format that we're using here it will provide   the correct filter and then i also want to make  sure that it is less than i want to make sure   that it is less that start date is actually less  than the due date here because i want to show this   less than r3 right so less than or equal to r3 and  that's going to do just right that so any dates   that are less than if i want to show data dates  from that right or up until that time i want to   show less than if r3 is blank i'm going to show  less than all dates which is going to use a large   number otherwise if it is not blank we're going  to show less than or equal to the due date of r3   and also likewise in the due date i want to show  very very similar this is for the start date   right i want to show the due dates of if the due  date is left blank then i want to show less than   or equal to a large number right just to make sure  that every single due date is considered otherwise   i want to make sure that the due date is greater  than or equal to r3 i want to make sure that it's   greater than all right and that's going to provide  us with the proper date filters so we can do any   start date from 5 1 up until due dates from 7 30  and that's going to show us only those tasks if   i were to refresh that it's going to clear out all  projects all statuses and all staff and it's going   to show us a list of those staff okay if i were to  click on here again or click on here it's going to   show us individual mind maps for the individual  staff a single staff at the time otherwise it's   going to get too messy right so we're going to  show this and we can show this i don't believe   i have too many tasks assigned for these but we  can show individual tasks for these projects and   they're going to be automatically spaced out  and that's what we're going to go into first   okay so the first thing what i want to do is we're  going to show you on this refresh how do we create   this list of staff based on the criteria here well  the first thing what we need to do is we need to   have some samples notice i've got some shapes here  i've got a sample shape called mm sample or mind   map sample and that is for our sample shape we're  going to take that sample we're going to create   duplicates from it and we can size them according  so the size of this doesn't necessarily matter vba   will take care of the size and i've got a picture  location here this will show either the icon   or the picture of the staff and it's going  to call it staff sample so we're going to   use these as samples we'll be duplicating them  with vba and then vba will place them accordingly   and also place the text i've got some lines and  connectors which we'll go through and i've got   some icons here now these icons are going to be  pulled directly from the admin screen so what the   macro is going to do it's going to look it's going  to look loop through all these icons it's going to   look for an icon and if an icon is found it's  going to put them here but first it will delete   any icons here so for example if i were to delete  these icons and i want to run that macro one more   time all i need to do is click refresh and those  were going to reappear right here because they are   copied deleted and copied through the macro and  that way if we decide to change one of these icons   all we need to do is then click refresh and that  change will be reflected automatically and it will   be showing up here and both in the tasks along  with previews so it's very easy to change those   icons dynamically and i'm going to show you how  to do that all right so let's get start getting   into the macro and see how we do that we're also  going to go over how we save how we add new tasks   how we had delete and how we load tasks simply  by just clicking on a task icon to load the task   details so we're going to get into all of that so  let's go ahead and get into the developers right   if you don't have that available of course you  can just click alt f11 to get you directly into   the visual basic we have three different modules  first application macros having to do with the   admin screen the mind map macros that's going to  be building our mind map and then we have the task   manager which is going to be able to the macros  which will of course we're going to be able to   save delete update our tasks okay so let's get  into the application macros these are going to   be the sum that we worked with on the admin we  be able to actually add an icon here if we want   to we will be fy i decided i want to change let's  say i wanted to change tasks to this little icon   here i could do that right here and just click  ok and then as soon as i refresh it we're in   here we're going to show you that brand new icon  it's going to be available here so we'll then see   our new projects has this icon so we can easily  change our tasks to our icon or projects icon or   anything we want in here all we need to do is just  change it right in here so adding a new one so   that's going to be a really cool i'll go ahead and  change it back to our tasks the one we had here   so we can see we can change icons on demand  very very easily and we can do that with the   macro well the first thing that happens is i've  got a selection change event right when i make a   selection here something happens right first thing  that happens is i want to color this row we can   use conditional formatting to do that so i'm going  to highlight that and i go into the conditional   formatting we're going to see there's a specific  rule that's going to be based on t1 row so that's   selected row that row is going to be placed  directly in t1 when that specific row 8 through   12 either row is goes into cell t1 i want that  color that dark blue fade with a white font and   i want that to be bold so it's fonts going to be  white and of course it's going to be bold type and   we're going to put that so our macro is going to  take care of it our macro when i make a selection   change it's going to go directly inside this cell  t1 over here you see that in the upper right okay   that is the icon row that's selected it's also  the icon number so actually excuse me t1 which   is here so nine so as soon as i select something  else it's going to be gone however if i make that   selection again you see t1 now takes on 10 in the  upper right corner so that is going to handle our   condition forming and that handles on selection  change so the selection change event is going to   come directly from the admin screen so when  i click on admin we have the first thing is   selection change and down if we scroll down here  we're looking for specific cells when we make   selection change to those specific cells it's  going to be anywhere from e8 all the way to f12   e8 through f12 so when i make that change right  here on when i make a intersection basically if   not intersect meaning i make a selection anywhere  on the cell in that range then i want something to   happen the first thing what i want to make sure  is i want to place whatever the row that we've   selected directly into t1 this will trigger the  conditional formatting triggers the conditional   formatting so we have that next up i want to  focus on some shapes as we saw there's two   shapes here two little buttons here one is our  add icon button and the other is our clear icon   button if i want to clear the icon like all i do  is just click delete and that's going to clear   that icon notice the icon's now been removed  if i want to add it again i just click plus   and then i'm going to add that icon again okay  so how do we add icons dynamically well the first   thing what i want to do is make sure that these  buttons are placed directly in the right place and   i've got two buttons here of course both of those  and we can do that with the following lines of   code right so once we make a selection and we add  that row then what i want to do is i want to focus   on that add icon button so that button is just two  shapes and grouped together and i've grouped them   and given them a specific name that's the name add  icon button that i've given it to with that button   i want to place it directly in column g based  on that target row that's going to be that   left position i want that top position also in g  in the toggle row and placed in that top position   that's going to place it and i also want to make  sure it is visible and now regardless of whatever   the user clicks i want the first thing what i want  to do is if that add icon is visible i want to   make it hidden that way it's hidden no matter what  they select then only if they make a selection   within this range do i want it to appear otherwise  it stays hidden and it's going to the same thing   for that clear icon button that also if we select  anything we want that to hidden if we select only   those shapes we want that clear icon button to  show up but i want to show up a little bit to the   right this time not exactly on the left because i  don't want them to overlap so what we're going to   do with that clear icon button we're going to show  it up the left is going to be based on the left   position of g plus we're going to add 16 pixels  to the right so that it shows up a little bit   to the right and it's going to have the same top  position then i also want to make sure that that   is visible there have been two macros that have  been assigned to that else it means if they click   anything outside of this range i want to make sure  to clear t1 and that's going to get rid of that   conditional formatting great also we want to show  a color palette on selection changes if i want to   make a color change to one of these two news or  status if i want to change the color to do maybe   i want to change it to i can use any color inside  that or i can use of course any color that i want   as well so if i want to use like a yellow to do  i could do that and that if i decide to do that   and we go ahead and click my map and we refresh it  again and then i don't think we need to do here we   go so we get now we get a color for the phone  meeting is the yellow so we can easily add it   and of course we refresh that it's going to change  those icons now we've already updated those icons   so we see that it's now in yellow and we have  that updated icon so we can quickly and easily   change the colors very very simply through either  this pop-up or with colors so it's going to match   whatever color is in here so this color palette  this is called the color palette right and i want   that display but i only want that displayed if  they've selected a particular cell anywhere from   c8 all the way through c12 so we're going to  take a look at that that's going to be based on   the selection change of c8 through c12 with that  color palette i want that to show up on the c and   the target or the left right i also want to place  it on the row below they've selected right if they   select this row i want to place it directly on the  row below a long c column to do that we're going   to base it on that target row but plus one meaning  one row down that's where i want to place the top   position of that color palette i want to make sure  it's visible and i want to bring the order to the   front why is that important because i don't want  it to show up under these icons right i don't want   these icons to show up above it so i want this  color palette to show up on top of these icons   right so that's where the z order is going to come  in handy so we want that means going to place it   on top that z order is going to bring it to the  front meaning bring it above all other shapes   again or also if that if we select first thing we  do if that color palette is visible we are going   to make sure to hide it when we select anything  else i only want that color palette showing up if   we make a selection on that range okay so that's  going to hide that's what we do here if it's   visible that color palette we're going to hide it  great so that's it for the on screen as far as on   the admin screen that's all the macros in there  based on the selection change if we go back into   the application macros we see that we had a macro  called add icon that particular macro is tied to   this particular button right here if we assign the  macro to the group you won't see any macro however   if we click inside zooming in and if i click  inside any particular shape here holding down the   control we will see that we have a specific macro  that's been assigned to any button here so let's   take a look inside the button here any kind of  macro inside this we can see that for example this   one here we right click and we assign the macro  we see that we have that called add icon we click   edit we're going to see that there's that same  ad icon and so that's the macro that i've tied   to this button here basically it's a shape and an  icon grouped together and give a name called add   icon button when i select any particular shape  inside that right inside that group if i click   assign macro we see it's the add icon add button  okay so we get into that macro what do we want to   happen we're going to focus on the admin screen  i want to have some the selected row is going   to be t1 right i've got to know what row we're  associated what icon right first thing i want to   know is what row in this case it's row 8 right  so knowing that row is going to help us place   i want to place both the thing let's go ahead  and add that staff icon back in right here so i   need to know where to place that icon and i also  want to place that name of that icon directly in   f and whatever the selected row is so having that  selected row inside a variable is very important   first thing i want to do is also determine if  there's any existing icon there i want to remove   it now each one of these icons has a very specific  name this first one this staff this is our first   icon we're going to call this icon one second  one's going to be called icon 2 here let's go   ahead and add that in everything's so small icon 2  and then icon three i also want to show that task   so gonna go in order one two three all the way up  to five so i know that if i'm going to be removing   an icon in row eight how do i know it's icon one  well all i need to do is simply deduct seven right   so if i know that we're under eight if i deduct  seven that's going to leave us with one so i   know the icon number that's associated with this  called icon one so to do that all we need to do   is first remove any existing icons that might be  associated it's going to be called a name given a   name called icon and the selected row the first  one will be 8 minus 7 that's going to be icon   1. i'm going to delete that if it doesn't exist it  will create an error so therefore we've wrapped it   in on air resume next and on error to go to zero  then what i want to do is i want to set the icon   i want to have the user browse for that we've  already defined the icon file as a file dialog   so we can then set it to the icon file is equal to  the application file dialog mso file dialog file   picker right that's going to allow us to pick  a specific picture file then we can associate   with that so with the icon file i'm going  to set a title called please select an icon   i want to give it a filter i don't want the  user to be able to select any type of file   i only want them to select on picture files so  we're going to filter by those picture files it's   going to be any type of a name using the asterisk  jpg any type of a png with any type of a name so   we're going to use that wildcard before the period  and then the extension of jif jpg or gif where   i only want one associated item and then allow  multi-select false of course just a single item   that allows you to select if they don't select  anything we need to allow them to get out of it so   if show one does not equal negative one if it does  equal negative one that means they have selected   something i don't know why it seems kind of  confusing but that's the actual truth so if show   it does not equal negative one meaning they click  the cancel or have not selected anything we're   going to go to no selection it's going to skip  all that and go right down if they have made a   selection what i want to do is i want to put that  file name what is the name of that file i want to   put it directly inside f8 or f and whatever the  selected row is so we're going to do that here   admin f and the selected row value is simply the  directory meaning the file name of that selected   item this is the full path of that file they've  selected when we extract only the name from it   we're going to wrap it directory around it and  that's going to give us just the file name then   what i want to do is i want to then insert that  icon right insert i want to take that icon i want   to put it directly inside column right here column  e and i want to place at the beginning of column e   so what we're going to do is we're going to insert  that icon using the file path of the selected item   one using that specific file path i also want to  give it a very specific name once i've inserted   it called icon in the selected row minus seven  meaning icon one two three four five it's going   to create that icon now we've placed that icon on  the sheet but we haven't sized it and we have not   positioned it so that's what we want to do next  we can focus directly on that brand new icon by   calling it out by name the name that we've given  and assigned to it we're going to place that left   position and the admin and the column e and the  selected row plus one meaning we're just gonna we   don't want it exactly on the right we want to move  it slightly over on right not on the left but a   little bit over to the right i also want to place  the top position also on the same column and the   same row and the top position plus one i want to  give it a very specific width and a very specific   height making it square using width 12 and height  12. and then i want to make sure it's visible   that's it that's all we need to do is add an icon  now notice that we have clear nikon to clear an   icon it's very simple all we need to know is  what row that's associated right if i know   that the selected icon rows 8 you see when we  selected something else of course when it's 8   right then i know that the icon number is going to  be icon 1. that's the one we want to remove and so   we can determine that by based on the selected row  so we're going to get that selected row inside t1   this icon clear this is the macro that's been  assigned to that clear button and then what   i'm going to do is simply delete it if it doesn't  exist it would create an error so therefore we've   wrapped it in honor resume next and on error go  to zero that is it theoretically we could clear   the name as well all right also we want to be  able to set the status color there's another   macro remember the macro that's associated here if  i were to click on a color here and i want to set   a color here i could do that now what i want to  do is set that color there there's a macro that's   been involved so when i click on here we click on  any individual shape inside this group of shapes   and we sorry it's off the here we want to assign  the macro to that we can see that it's been called   macro admin set status color so that's the macro  that we're going into right admin set status color   all we need to do is look to the active cell  whatever sheet we're on we're going to change   that interior color that interior color is  going to be based on that selected shape how   do we know the name of the selected shape we can  use application color something we're also going   to be using a little bit later i want to know what  the fill color is of that shape right it's right   here fill for color rgb we're going to take that  color and we're going to make the interior color   of that shape then also i want to do is hide that  color palette that's all we have to do last thing   is simply browse for staff picture folder this  is the macro that's simply been tied to this   here so that way when we browse for the file we  can simply browse for it and locate that staff   picture folder here and then we can just browse so  that's going to browse for that that's the macro   that allows us to browse for that folder it's  going to place that file path directly inside d3   so to do that we can do that with this browse for  staff file picture this is the macro that's been   assigned to that button we're going to mention  the bill folder not really the bill folder right   should this be the staff folder let's make that  change just so we're a little more accurate   as you can see sometimes i just copy and paste  this code as we all do and then i'm going to find   bill right now i'm going to place with the word  staff because i like that a little bit better   and just play staff okay and then replace all that  looks a little bit better it's three replacements   so browse for staff picture folder the staff  folder equal the file dialog the staff folder   is equal to the application this time we're using  a folder picker folder picker okay it's different   and then what i want to do is i'm going to set the  style folder browse for got lazy here staff folder   and not lazy busy busy allow for the multisec  just one folder we want them to select again if   they don't make a selection we're going to go to  no selection whatever that's selected that full   folder path we're going to place directly in d3  that's it that's all we need to do and that's it   for the application macros relatively simple and  straightforward let's go ahead and get into the   mind map map and i'm going to start out with the  first one called load staff that is the macro that   we're going to focus on that is this macro that's  going to basically allow us to refresh everything   clear everything out and get and determine all  the staff that are associated based on the filters   that are set up here remember if i select another  staff it's only going to show these staff if i   refresh it's going to go back to all staff but if  i select here it can only be a given staff here so   this macro is the one that is tied to this button  here if we take a look inside this button clicking   a slide macro we see it's called mind map load  staff that is the first macro that we're going   to go over in our mind map module here load staff  okay the first thing what i want to do is reset   all those filters in h3 j3 and l3 those are our  filters located right here h3 j3 and l3 i want to   set it to all staff all projects and all statuses  right because we want to clear everything out make   sure there's no associated filters we'll keep the  dates the same that's fine so that's going to let   us load that so we're going to do that first  that's going to reset all of our filters there   at least for our staff projects and statuses i  also want to remove all the mind map shape now   we're going to going into a little bit naming in  a little bit but basically what i want to do is if   i've got tasks displayed projects displayed i want  to remove everything right so we've given them   very specific names right so that way when i click  refresh i can remove all the names associated and   even the staff names are going to be removed and  replaced so to do that we're going to do this so   we're going to remove all mind map shapes so  we're going to loop through all of the shapes   inside the given sheet now we're going to use mm  shape as our variable here mm shape has already   been defined here as a shape so we know it's a  shape so for each shape inside dot shapes i want   to check for something i want to look at based on  the name obviously i don't want to delete every   shape on the sheet i don't want to delete this  these shapes or this refresh or this icon or this   text up at the top only these shapes or especially  any of my sample shapes or icons only the shapes   associated with the mind map so each mind map  shape has very specific naming involved that no   other shapes have so we're going to look for is  mm staff these are all the shapes associated with   our staff so if i right click here we see this  is called mm staff 1. this is called mm staff 2   and so on and so forth so i want to remove every  shape associated with this if we take a look at   this this is called mm staff picture one so both  of these shapes start with mm staff and also this   one is mm staff expander staff too so all these  shapes even this little one here this connector   mm staff connector so all of these shapes start  with mm staff so that way we can remove all   them very easily using this so mm if the name  contains contains mm staff is greater than zero   meaning the shape does contain name then we're  going to delete it delete all the staff shapes   now for our projects something very similar  if i select on here and we take a look at the   projects here this one's called mm proj broj  this is called mm proj icon and then mm proj   expander so you get the idea they all start  with that and then they end a little bit   but all of our project shapes start with mm  proj so i want to be able to remove those also   so that means any shape that starts with mmproj  is going to also be deleted the same thing with   the task group right if i got a group of tasks  here and notice called task group right so this   group of tasks i want to remove our connectors  here are called mm projects so those are going   to be associated with the project auto remove  and then i want to move in this group so any   groups that start with task group i also want  to remove so anything that starts with task   group we're going to remove that will remove all  our task groups and then also any icons yes i do   even want to remove these icons because each time  when we run this particular macro those icons are   going to be brought directly from the admin screen  that means if there's any changes to the icons   the new ones are going to also be brought here  so these here are called icon 1 icon 2 icon 3   and icon 4. so i also want those removed as well  so any shape that contains the word icon is going   to be removed so we're going to loop through all  those and delete everything so that's getting   great so if i were to stop that code right here  and then i would go ahead and refresh that we   see that let me take a look now everything's been  removed even our icons are removed everything's   been cleared out okay so if we continue with  this code here we'll see that everything   automatically updates and our get our staff is now  updated let's continue on with the macro now that   we know that all the shapes have grown so what  i want to do is first again as i mentioned you   i want to notice if there's any changes in those  icons we already saw when i tested it that i made   a change to these icons and those changes were  automatically reflected in the mind map to do   that i want to make sure that we're going to loop  through all the icons and if an icon does exist   i want to place that icon directly inside the mind  map sheet and the reason is i want to that i don't   individually pull them from the admin sheet each  and every time it's it's a lot faster if they're   already on the current sheet so i want to make  sure that they're on the current sheet already   to do that we're going to run a loop and i want  to run the loop where there's up to five different   icons as you're seeing here we have places  for five different icons if we want to add an   individual icon we could so i want to loop all the  way from 8 to 12 or 5 different icons to do that   we're going to run an icon number those are the  numbers that we're going to be assigning to the   icon i'm going to check that e and the icon number  plus 7 meaning starting on row eight starting with   one if it does not equal empty we know that e  contains a name right that icon type is a name   so i wanna make sure if this is not empty then  go ahead and insert it right so what we're gonna   do is we're gonna take that icon number and we're  going to copy it right we should probably add on   air zoom next if there's if the icon disappears it  could create an error so probably should do that   admin shapes icon icon we're going to copy that  icon wherever that icon is then what we're going   to do is we're going to activate the mind map  sheet with the mind map shape we're going to   activate that then what i'm going to do is i'm  going to select on a cell this keeps from i want   wherever i want to place it i want to place it  the first one in in a plus the icon number so the   first one is going to show up in a22 i'm going to  select that cell once i select that cell remember   if we cannot select that cell without first  activating because we're not on the sheet and   we're running this macro it's going to create an  error anytime you use select you must make sure   that we've activated the sheet just to be sure  in case that we're trying to run this macro from   another sheet it would create an error we can't  select a cell on a sheet that we're not that's   not currently active we're going to then paste  in that macro okay if there's any air we're gonna   let it know so we could probably do on air let's  do on air going next here too just in case there's   an issue with the air with the uh particular  icon or the icon doesn't exist they would create   an error so we're gonna have that there great  so we're gonna paste it in there all right so   we're gonna this is simply going to copy all the  icons from the admin screen and bring them into   our mind map sheet i also now what we're ready is  we're ready to place all of our staff here and so   what i want to do inside here is i want to get our  original top position our left position somewhere   around f5 but not exactly right a little bit off  that so what i'm going to do is i'm going to set   our initial left position on f5 but not exactly  i want to move it over to the right a little bit   i'm going to increment that 30 pixels to the right  so we're going to do that that's going to set our   initial left position set initial left position  i also want to set the initial tom position now   that top position is going to be based on our also  on f5 but on the exact top position so this will   be set initial top position so once we have that  we can then continue on i also want to get that   picture folder if we're going to be extracting  those pictures directly from our picture folder   i need to know the full file path of that that  full file path is also located in the admin so   what we can do is we're going to set that picture  folder based on the admin and the picture folder   if we take a look inside back inside our  admin we take a look inside this we see that   d3 has also been assigned a name bridge called  picture folder picture folder also just to make   sure that the named ray chair picture folder  should be different than your name bridge here   uh right here called pick folder notice that these  are different trying to keep them the same i have   had issues before where the named range and  the variable name if they're the same it could   create some issues so we want to make sure the  pick folder is different than picture folder i   also want to put a backslash onto the end of that  because we're going to be adding the picture name   that picture name is going to become directly from  our staff list right here here it's going to be   pulling them but i want to pull that from here so  notice each staff has a name as we added that one   in so continuing on to with our macro so we're  going to set our initial folder along with the   backslash we're going to run the staff filter what  i want to do is i want to base it on this filter   here if there's any staff that have been based  on a filter here i want to run advanced filter   based on all the data all the way through row  46 here and i want to run the criteria based on   any criteria all the way from o2 through s3 and  i want to have unique staff that unique staff   is going to be here right i want all the unique  staff associated with it to come down here then   what i want to do is i want to have the picture  associated with that staff appear here now notice   that that picture is not in our original data  that picture is located in our staff list here   so what we need to do is we need to extract that  picture and we can do that with a named range to   help us out so we have two name branches that are  going to help us out we have staff name which is a   dynamic named range and i also have another one  called staff picture here and so right here is   a dynamic named range for staff pictures so we  can see the staff picture right here so all we   need to do is simply use the index and determine  on the staff ids we're going to close this out   and so what i want to do inside our tasks is  create a formula so as we load in i want to create   a formula right here and i want to bring down this  formula so it's going to bring down those pictures   so what is this formula first of all we're going  to check to make sure that if error we're going   to index the staff picture right that's where we  want to locate that staff picture and we're going   to base it on the staff id in u3 and i'm going  to base it on and then what i want to do is i   want to show that single column because it is  that stack staff picture that i want to extract   if there's an error just show blank so as we bring  this formula down here using vba it'll extract all   those pictures then i have the staff id the staff  name and the picture all present all ready for us   okay so that's what we're going to do and i'll do  that inside the macro so the first thing we want   to do is i want to clear the contents of any i'm  going to delete all the contents here right just   clearing that out all the way from u to w to make  sure that we don't have any old data so range u3   through w999 clearing any previous results i want  to get the last row of our original data using and   xlab then what i want to do is i want to make sure  that we actually have data in the last row it's   less than four that means we have no data right if  it's less than 4 right in this case our last row   is 46 but if it's less than 4 that means we have  no data we can exit this up then what we'll do is   we're ready to run our advanced filter based on  the original data a3 through k in the last row a3   all the way through k actually we're going  to use a blank we probably don't need to use   blank i could probably use we could probably use  just here all the updates but we'll go through   j and go through j but either way it's fine if  our results keep this in mind if our results   here unique staff include a blank here then we  do need include blank but we can use in this   case we'll use j it's sufficient enough we don't  need to include that so we also want that criteria   that criteria range is going to be o2 through s3  o2 through actually s4 i'm going to change that   o2 through s4 to make it a little more clear o2  through s4 and that's going to bring because we   need to make sure that we're going to include all  the dates associated with o2 all the way through   s4 because we need to encompass all that data  right we need to include both the due dates and   the starch the status product name so that's  our criteria and i want those results to come   u through v u through v now that staff picture  will be added on it later in the next step so   the results though this is the first step of the  results all we're going to be doing is bringing in   both the staff id and the staff so those results  are going to come directly in here u2 through v2   once we have those results i need to check  for the last row that last row using column m   is row 8 using column u so i'm going to check that  to determine the last row that last result row is   based on column u if that last results row is less  than 3 that means we have no staff for that period   of time and we can exit out of the sub there's no  staff to load if we do have staff what i want to   do is i want to take whatever that formula that  we created to extract that staff picture this   was called the staff picture formula and we want  to place that all in all of the rows associated   with that in column w so w3 through w in the last  results row formula equals whatever formula is in   w1 and that's going to bring it and automatically  calculate it and the reason we do this is we don't   really want formulas floating in an entire column  we don't have any staff right i only want formulas   where they're needed right we don't want a  workbook full of formulas that are not being used   so i want to make sure that they're being used and  to do that we only place them in the cells that we   need so in this case w3 through w8 formula equals  whatever's in w1 formula that's going to bring in   that formula and show those pictures we need those  pictures because this is those pictures that we're   going to be able to display directly inside our  shapes here because i want those pictures when i   take those picture names and i combine them with  that picture folder along with the backlash we   then have a full file path and we can then insert  that picture and then position it accordingly   i'm going to do that with the following so then  what we're going to do is we're going to loop   through all those results so we're going to have  our results here's our results all the way from   3 to 8. i'm going to loop through those adding  shapes based on those individual staff so for   the results row equals three to the last results  row we're going to create these staff shapes but   first i need to extract some information and put  that information into variables so the staff id   is simply going to be whatever's in you and the  result row right that's our staff id coming from   column u we then want our staff name that's going  to come from column v and then i want to have   that staff picture and that's going to come from  column w putting all those into string variables   then what i do is i'm writing now i've got all  the data that i need now i'm ready to create   those shapes so what we're going to be doing  is taking our sample shape right here called mm   sample i'm going to be duplicating this and then  i'm going to be renaming that shape accordingly so   the first thing we want to do mind map with that  shape mm sample we're going to duplicate that   and then we're going to give it a very specific  name calling mm staff remember we need to make   sure we give it very specific names so that we  can remove it very very easily when we need to   clear it out so we're going to have mm staff and  we're going to be making sure that it is a unique   name so we're going to tie the staff id to that  because each staff has their own unique number and   therefore we know that the name of the shape then  is going to be unique once we've given it a name   we can then focus on working with it so with my  map shapes that brand new shape that we've created   we can then position it we're going to base it on  the left position that first initial left position   based on the top position that's that first  position that we've created what i also want   to do is i want to place the name inside the text  right their name text frame text range text equals   the staff name we're going to set the staff name  as text okay then what i want to do is i want to   auto shade if we notice that notice these shapes  mary smith has a smaller one david davidson has   a larger one right so i want those shapes to  be dynamic the widths i want to be dynamic   however if we just auto shape them completely  and don't set a height they're going to have   different heights i want them all to have the same  height but i want to have them different widths   based on the name right so notice each one has a  different width and that is because we are using   this one auto size text frame ms autoshape we  want the shape to fit the text right size shape   to fit the text so now the shape has been fixed  now this is going to be both horizontal and   vertically both width and the height are going to  be out of size but i don't want that i only want   the width so what we're going to do is we're going  to set a very specific height we're going to set   that height to 18 pixels okay so that way they're  all going to have the same height but they'll have   different widths and that's exactly what i want  it's going to give it a nice look of customized   so we don't have any extra spacing right so  it kind of gives it a little bit better look   great so now what i've done we've already placed  the shape so now it's time to replace that picture   now notice this particular shape that we have  here if we take a look at this and we look in the   format here we'll go ahead and format this shape  i'll right click and format we see that in the   text options here in the text options i've given  it a very left margin of 0.3 i've given it space   for that picture right if i were to reduce that to  0 it would be all the way there so we don't want   to do that we want to make sure we have space and  i've given a little bit of a right margin of 0.03   that's giving you some spacing on the right so  it's not all in the right and we've got plenty   of space for that picture 3.3 there so we see how  we've created that sample shape once we duplicate   that sample shape all the shapes are going to be  accordingly automatically all right so we've given   it that so now what i want to do is i want to add  the picture first of all we need to make sure that   the variable that we've created here staff picture  doesn't equal empty if it does we're going to use   the icon if it doesn't we're going to add the  picture so if staff picture does not equal empty   then staff picture exists all we need to do is  combine that picture folder that we've already   defined all the way up here we've defined it  directly up here one of the first things we did   was define that picture folder right up here okay  so we've defined that already so now all we need   to do is simply add it in here so how are we going  to do that so what we want to do is i want to get   that full picture path right here that picture  path is simply equal to the picture folder along   with and the staff picture those combined as our  full picture path i want to check to see if it's   an accurate path if it's inaccurate we're going  to use the icon if the directory of the picture   path vb directory equals empty it's not an  incorrect path we're going to go to bad path   that's going to skip all the way down here and go  right to here but if it is a good path what we're   going to be doing is we're going to be inserting  that so what i'm going to do is mind map shapes   staff sample i'm going to create this i need  to then duplicate this one right here this   shape right here called staff sample i'm going to  duplicate that and give it a very specific name   duplicating it giving a name called mm staff  picture and the staff id again giving it a very   unique name then we're going to work with that so  i'm going to set that left position to the same   left position i'm going to set that top position  based on the top position of whatever our current   shape is right remember we've did the mm staff i  want to know the top position of the shape that we   created here what is the top position of the shape  remember the shape that we created here what is   that top position it's going to be mm top i want  to make sure we give it that same top position   then i want to fill it right i want to put that  picture inside that shape we can do this with fill   user picture and then the picture path it's going  to be the fills the shape fills shape with staff   picture so that's all we need to do and then all  we need to do is just set the height okay then   what i want to do is i want to set the height to  18 and the width 18 something very very specific   what that's going to do is set that square making  sure that each one has a very specific height and   a very specific width 18. great so that's it so  then we've added the picture but what if there's   no staff picture right this is only if the staff  picture does not equal empty what if it is empty   that's going to be else or what if there's a  bad path if there's a bad path or there's no   staff picture then i want to use that icon  remember that new staff we added had that icon   right or remember when we create that that false  path right we know that as soon as we refresh   that page here we can then see that the icons  are used so in that case we want to make sure   that we're using the icon that's sorted here and  that's going to be icon 1. that's the icon that   we're going to be using in case the pictures are  incorrect so to do that all we need to do is just   set up that icon i'm going to do that mind map  shapes icon it is that same icon that's located   in this sheet called icon 1. we know it's there  so icon then we're going to duplicate that and   what we're going to be doing is we're giving also  a specific name called mm staff icon and the staff   id same name we're going to do the same everything  else with that we're going to place it on the left   a little bit over to the right and also we're  going to give it a little bit lower than the   top position giving it a very specific height and  a very specific width that's what we're going to   do if it's an icon so in case there's no picture  we can add an icon still have a very professional   look even without a picture great so that's it  so now what we want to do is we've added in this   shape we've added in this picture here and put it  in now what i want to do is i want to add in this   expander right and i want to add in this expander  right i want to add a connector and expander   so we have one here it's called this one called  staff expander sample it is this sample shape   that i want to then duplicate and i'll place it  directly in the middle here then what i want to   do is i want to take this connector here this  staff connector sample i want to duplicate that   and i'll place it and connect both of these points  in here so how we're going to do that well first   of all we're going to add in that staff expander  button this is the one that's that with that plus   so to do that we are going to do this the mind  map we have that sample right here called staff   expander sample we're going to duplicate that and  again we're going to give it a very specific name   also starting with mm staff expander staff and  the staff id we're going to duplicate that staff   expander it's really a project it's a expander to  expand all the projects for that specific staff   and then all we need to do is just place that  once we do it we're going to play give it that   left position now i want that left position to be  placed based on the left position of this so it's   going to be based on the left position of this  plus the width of that plus another a few spaces   and then right in the middle so remember it's the  the left position of this plus the width of that   plus eight pixels eight pixels over to the right  right if i were to change this to let's say 18   right and you see that's going to be even farther  away right so you can see it's farther away so   that's how we can do that so it's relatively  simple just to update it and give it a new look   okay i also want to give it i don't want to  put it directly on the top i want to put it   a little bit off the top not not directly on  the top right i want to move it down i want to   place it directly in the middle so we're going  to use the top position of that shape and we're   going to place it two and a half pixels down  that's going to bring it down a little bit   then what i want to do is i want to tie a macro to  this remember there's a macro if i click on this   there's a macro that's associated that it's going  to expand the projects associated with that staff   based on the dates so there's got to be macro the  macro which you're going to be getting into next   called mind map load project so i want to assign a  macro to that okay great so then we've added we've   added in this particular icon now all we need  to do is add this connector in this mm staff   connector we're going to use this sample here  called staff connector sample i'm going to add   this in and then i'm going to place it directly  in here so to do that we can do the following   so mind map shape staff sample we're going to  duplicate that connector that sample connector   we're going to give that a specific name called  mm staff connector and the staff id also another   unique name then what we want to do is we want to  place that i want to place that in a very specific   point now if i zoom in we need to know what points  when we add connectors we need to know what points   there's several points that we can do based on  the shape we're going to it goes counter clockwise   this is 1 this is 2 this is three and this is four  so when so the beginning connect based on this   shape mm staff one is point four again one goes in  counter clockwise here okay so when we select on   this this is one this is two this is three this is  four always and counterclockwise now we also want   to place it on this shape here right so i want to  place the other end the end connector on this hip   so if i drag this over we see one two three so i  wanna place it on three right counterclockwise so   starting at one two three four going four and then  going to three so that's just what we're going to   do directly in here so our connector format the  beginner connected where we connect it to we're   connecting it to this shape directly or mind  map shapes mmm stuff that's the first and we're   connecting it to point four remember colors one  two three and four and the second one we're gonna   go one two three counterclockwise so the second  shape we're gonna the end connect connector format   and connect is going to be placed on this shape  that's expander shape and we're going to put it on   position three remember if i were to change this  it would probably look quite ugly but if i would   change it to here right and i refresh that and we  can see let's go ahead and it doesn't change that   oh let me change it here let's put it to let's  say six here i like that better so if we were to   refresh it it's going to look kind of kind of ugly  but you see the point right well the point is now   it's on the sixth spot so let's count again here  just so we zoom in and we know all the spots one   two three four five six right so you can go all  the way around so you just do that and you know   that it's position three three is the correct one  that we want on there okay so we see how that not   sixty three three okay so we see how the points  along we know how to count them counterclockwise   to find the right point you want to connect  then all we need to do as we loop is we want to   increment 50 right so i'm using 50 if we want ours  if we have let's say we have a lot of staff right   we don't want them so far apart maybe we only want  them to increment maybe 30. so if i were to change   this to 30 they're going to be a lot closer  together so see how they're closer together   right so that's going to be good if you have a lot  of staff that might be helpful we'll keep it at   50 for our purposes so all we need to do is just  increment that top position as we loop through the   staff and then next result row so that just loops  through the individual staff that's all we need to   do to then create this great so now we've got our  but now how do we actually create projects when   i click here what do i want to do well i want to  add in a specific staff right here in our filter   when we run that advanced filter what that's going  to do is going to take our criteria and put it in   here and it's going to return only those projects  in here that are associated with that staff and   then going to loop through those projects and  i'm going to create those three shapes based on   the project in those loops and then i'm going to  add a connector and then add that so that's what   we're going to do inside the next macro and that  is the one the same macro that we saw mind map   load projects it is this macro that's tied to that  particular shape that we said that expander shape   that we had there right here this is the macro  that we added to that expander right on action   remember when we click here there's a macro that's  tied to that button is the macro that's going to   create these shapes here that's the macro that i  want to go over for you now mind map load projects   so the first thing what i want to do is i want a  unique project id as a string project name as a   string and the project account i want to know how  many projects are associated with this it's going   to help put some spacing the first thing i want  to extract some data i want to know the staff id   what is the name right i want to know what is the  staff id and i also want to know the staff name   how do we get the staff id if i know let's take  a look let's shrink this down and take a look at   the original if i right click on here i see that  this is called mm staff expander staff one right   we know the staff id is one if i click here we  see the staff two and then here is staff three   so what i want to do is i want to extract that  staff id so how am i going to get that staff id   i'm going to use the application caller that  is the name of the shape that called it right   and all i need to do is remove this mm staff  expander staff if i take if i find the name that   called it and i remove this and i replace it with  nothing what's that going to leave me with it's   going to leave me with just this one or just the  two or just the staff id so that's all we need to   do so staff id using the replace along with the  application caller we're going to remove this   next replace it with nothing and that's going  to extract only the staff id but i also need   the staff name i want to place that staff name  directly in h3 so how do we get the staff name   well i know there's a few ways to do it but one of  the easy ways we know that this shape mm staff one   we know the text based on that shape is the staff  name right if we extract the text this text right   here i know that's the staff name so why don't  we extract the name and that's why i love working   with shapes because look this shape a cell a  single cell can hold one variable right generally   one one data point but yet a shape can hold two  data right this shape can hold both the staff id   inside the shape name and it can hold the staff  name inside the text so a shape can actually hold   two at least two data points right you could  probably get more out of it but at least two   data points we can hold from a single shape and i  really like working with they're not only fast but   they can hold more data than a cell so that's one  of the reasons so we can do that so the staff name   is going to be based on mm staff and the staff id  that's that shape we're going to extract it the   text frame text range text we're going to get that  staff name directly from that and all i need to do   is just take that and place it directly inside  h3 so once i have that staff name i'm going to   place it directly in h3 and what that's going to  do is add our criteria it's going to automatically   add that name here based on the formula to our  criteria right here so we can do that right here   h3 can equal the staff name and i want to make  sure that we're resetting all the projects right   if i click staff name right let's say i click  here right i want to set notice that our projects   here let's do a small let's say a project here is  called fred's remove right if i decide i'm going   to click this again i want to make sure that  this goes back to all projects right if i hide   it so it's going to go back to all projects just  to make sure that this is reset so i want to make   sure that it's automatic on all projects so now  what i'm going to do is i want to make sure that   if there's any projects notice that we collect  i'm only want to show the projects one at a time   so what i want to do is i want to first remove any  other projects from any other staff that have been   created so to do that we can loop through all  the shapes and if we take a look inside that   we see this is called mm project one and then  project six so again the names are very important   even the icon and then project icon so i know  that any shape that's associated with mm project   is going to be removed so we're gonna loop through  all the shapes again mm shape this time we're   going to look for any shape with mn project if  it's greater than 0 we're going to remove that   shape i also want to remove anything that's called  shrink staff right shrink staff i want to remove   all other string staff what does that mean that  means i want to make sure that if there's any   other this particular is called shrink staff two  if i select this minus one it's called string   staff i want to hide all those right anything that  says shrinkstab i want to hide it and i want to   put my own notice that this is this one's called  string staff this one this one's called expand   staff right so i want to remove all this any other  strings tab if i click on this one this button   here i want to make sure that that gets hidden or  removed so to click that just undo that so we can   do that here so anything that says string staff  then i'm going to delete that remove all string   style or project buttons i also want to remove any  task group right if i click here there's a group   a group of these tasks called task group right if  i click another one here i want to make sure that   i've removed not only all the projects but all  the tasks associated with this particular staff by   doing this you see how all the tasks got removed  so i also want to remove anything that says task   group there also i want to if there's anything  if there's a visible let's say there's a staff   expander if it's greater than 0 i want to show  it right this one if mn stats does not equal   0 i want to make it visible what is that the  expander staff i want to show that to make sure   that they're all visible that means this one right  here this one right here when i when i click here   that plus is there but it's hidden right that  plus is hidden i want to make sure that it becomes   automatically visible right so for everyone that's  expander if it contains that i want to make them   visible so all the expander shapes including the  one we're on showing that visible making sure that   they're visible so we're going to loop through all  that to make sure that means we only want it now   all we need to do is really focus on that specific  one all we did there is simply clean this up right   that way when we select it because if i select  another step i want to make sure all this gets   removed right so that's how we're going to do  that so we're clearing that out all right so   now what we're going to do is we're going to run  our project filter we've cleaned everything up on   the screen now we're ready to again we're going  to do exactly the same filter we're determining   the last row if it's less than four we're going  to exit this up running our criteria the same   criteria except this time our result is going  to come in a different area a b 2 through a c 2   that's where our results are going to come i only  focus on the projects here a b 2 through a c and   again we can make this let's check our original  data right making sure that it is j not k right   that's just going to help but it shouldn't make a  difference it still works just fine so then what   we want to do is i want to determine the last row  based on a b right this is our last row because   i'm gonna have to loop through all the projects  and i'm gonna have to create shapes based on those   projects so i need to know the last row based on  column a b so if it's less than three that means   we have no projects exist for this select the  staff will create a staff but there's no projects   let the user know that there's no projects for  that so i want to count the projects i want to   know that it is this case right last row is five  i know that there's three projects all i need to   do is subtract two and to determine the number of  projects so our project count is the last results   around minus two and that's going to give us the  number of projects and put it into this particular   variable all right the first thing what i want  to do is i want to know the top position that's   where it gets a little bit tricky right if  i want to expand the project i want to put   the i don't want to put it all the way up to top  right i want to put it based basically i want to   center the projects if there's two projects i want  this project centered between that right and i   want to put it centered right it's almost centered  there but i want to put those basically or near   center just like this so that as we add them so  i need to determine first of all when i want to   determine the top position of here then i want  to determine the total number of projects right   then what i want to do is i want to split it in  two so that the same number of projects are on top   and the same number of projects are on the  bottom right so that it's split into that   so first of all the top position is going to be  based on the application collar what is the top   position of this this is the shape that color so i  want to know the top position of this shape right   here so if i know the top position in fact we  could probably make it a little bit less right   so it should be a little bit a little bit on the  top right if there's one single project it should   be right about there right so we'll make it a  little bit higher because we're basically on   the top position there and then what i want to do  to make it probably two minus two on that probably   minus two just to make it let's try it with one  probably it's almost perfect but let's take a look   at it yeah that looks pretty good a little bit  less there so maybe minus four or something so   what i want to do is i want to place it perfectly  centered there and i want to split that so the top   position here what i want to do is i want to let's  let's say we have about 50 pixels in between each   of the projects right so let's say we have about  50 that looks pretty good 50 pixels between the   top so i want to determine if i know that that  we have let's say three projects what i want   to do is i want to multiply that by the project  counts basically one less i want to multiply it   i know there's 50 pixels here between here 50  pixels so i'm going to take the total number   of projects 3 i'm going to subtract 1 because i  want 50 above and i want 50 below here basically   so 3 minus 1 is 2 2 times 50 is 100 so i've got  100 pixels total so that's what we're going to do   here so let's say our project count is 3 minus  one is two so we're going to get two times 50   right that's going to give us 100 i'm going  to divide that by 2. so it's giving us 50   pixels above and 50 pixels below so i'm going to  take that top position and i'm going to subtract   50 right so i'm going to subtract 50 that's going  to move it up subtracting it moves it up 50 and   then minus 2 that's going to make it almost  perfectly centered right there almost that pair   let's go to 5. i think it's going to be almost  perfect i'm kind of picky about these things   so it gives you an idea so we can see that as  we reduce it right so now we have it perfectly   centered that looks much better so it's perfectly  centered right we've got 50 above 50 below if   we're adding more projects right so take a look  at this one however take a look at this one   what if the top position is higher what if it  can't be up here so i need to set the limit   to the top position the top position this is  for projects right but notice this one's not   centered why is that because we need to set a  limit because if that top position is higher   then of row five then we need to set that limit  and that's what we're going to do in the next line   if oh actually the next line we're going to set  the left position the left position is simply   we're going to move it over whatever this left  position of this we're going to move it over   50 and that's going to give us our left  position if i were to increase that right   increase that it would move it over to the  right if i were to decrease it would be closer   so now what we're going to do is we're going to  double check that top position i need to make sure   in this case that if there's we can't split this  one right this let's say this is 100 above 100 but   it'd be too high up so what i need to do is i need  to check that top position if that top position is   less than the top position of this then set the  top position that minimum top position of row five   so we're going to do that right here if the top  position is less than the top position of row five   then we're simply going to set the minimum top  position equal to row five this sets the minimum   top position and this is a perfect example  because you see this cannot be split up there   and the same thing with this one here notice  it can't can't be split up at the top position   so we're going to bring it lower so this  sets the minimum top position great so we set   the top position we've set the left  position we know where to place these   now what we're going to do is we're going to place  this notice that this expander got replaced right   now it's called a shrink button so i'm going to  take this one this sample one it's called staff   shrink sample and i'm going to replace that  plus with the minus shape right so i'm going   to simply do that so to do that all we need to do  is duplicate this staff shrink sample i'm going   to duplicate that and i'm going to give it a very  unique name called mm staff strength staff and the   staff id and that's just going to give it a unique  name and what i want to do is i want to hide this   i'm not deleting this this plus here right this  plus here i'm just going to hide it i'm going to   simply make it hidden and then i can show it  visible we don't need to delete it and recreate   it we can simply just hide it and then show it up  right so when i collect here it's really going to   again make that visible so to do that we can  simply do this mind map shapes staff visible   this expander button visible equals false so  we're going to hide the expander shape next up   what i'm going to do is i'm going to take that  staff i want to place that that we just created   this string staff and we're going to place let's  call this place a shrink shrink button right that   shrink button in place okay and put it in place so  we're going to place that left position directly   based on the existing staff right i want to place  it this is the one we've just hidden the staff   expander but i want to place it exactly where  it's the same ones located exactly where the left   position of the expander button is and exactly in  the top position and since they're already sized   exactly the same we don't need to size it and then  i want to give it a very specific macro this macro   is going to be called hide projects we're going to  go over that next so this macro is very different   because in this macro we're simply going to hide  the project or delete the project actuality and   then display that background so it works very very  well okay so continuing on with the code now we've   placed that expander with staff shrinker we've  replaced it right now what we do is we're going   to loop through the results we've got our results  right here inside our projects i'm going to loop   all the way from three to our last row and we're  going to be creating very very specific shapes   based on those projects along with icons and along  with their own expander buttons so we can expand   the tasks accordingly for that specific project  and to do that we can loop through this so for   the result row equals three to the last result row  i need to get the variables and i'm all i'm going   to be doing is getting the project id from a b and  getting the project name from ac so we can do that   here project id through a b a b and the project  name through a c and now what we're going to do   is again i'm going to take that same shape that  we're going to be using throughout that mm sample   and we're going to be duplicating that to create  our project shape so we're duplicating that just   as we did here right here duplicating and giving  it a very specific name based on the project   this time we're basing it on mn project and the  project id being unique because that project has   a unique id then we're going to focus journaling  on that all i need to do is place it on the using   the top position and using the left position  which we've already defined up here right   we've already defined all the way up here the  mm top and then the mm left position so we've   the mind map top are already placed and the top  position is simply going to be i want to make sure   that the top position is going to be based on this  that's the icon here the top position is here okay   so now what i want to do is i want to add that  this particular we've created not this one here   not this one let's keep keep track here we're  moving on okay so this one i also i've duplicated   this shape but i need to give it text that text  inside there is simply going to be the project   name so to do that text frame text range is going  to be that project name so this is going to set   the project name and again just like we did with  the staff i want to automatically size it as far   as the width is concerned because that way we have  different widths for our different projects based   on the text so to do that all we need to do is use  auto size and then use shape text to fit and then   of course making sure that we do set the height so  that they all have the same heights of 18 pixels   now we can add that project icon and i know the  project icon that's going to be icon 2 and it's   located right here this is our project icon  we've already brought it over so that's what   we're going to be using right here all i need to  do is take icon 2 and duplicate it accordingly   and place it directly inside here so with mind map  in case there's no icon we can loop through that   and get rid of on-air resume next and the mind map  shapes icon 2 is what we're going to be focusing   on we're going to duplicate that giving it also a  specific name mn project icon and the project id   so now what we're going to do is we're going to be  focused working directly with that all i need to   do is place it on the left position plus one right  i don't want it directly on the left of the left   position just moving it over slightly to the right  so we're going to add one to that left position   and also what i want to do is i want to set the  top position based on the project whatever project   id this whatever this here called mm project six  i want to place it at the top position on this   plus one down whatever the top position of this  current shape is plus one so it's not right on   the top so placing it on the left in the top  position giving it a very specific height   and a very specific width that's all we need to do  that so that's going to place our both our project   and our project icon now what we want to do is i  want to add a staff connector i want to add a very   specific connector or here or actually our staff  connector here so i'm going to add a connector   here and it's going to be based directly on this  it's going to connect from the mm staff shrink   staff and it's going to go all the way to our  staff so we're going to use this connector right   here if we zoom into this i've created a connector  a little bit of a fake color from blue to green   and if we want to know how to do that all we need  to do is right click format the shape and we see   that it's a gradient line from blue to green so  given a basic blue green so we've given a name   called project connector sample project connector  it is that one that i'm going to duplicate it   and i want to connect it to this shape here  mm-stash rank 1 and i want to connect it right to   here so to do that all we need to do is do project  sample we're going to duplicate that giving a very   specific name project connector and the project  d it's going to duplicate our staff connector   let's connect our staff to our projects then again  just like we did before we need to assign it very   locations right we know the two shapes that we're  going to that are going to connect it together   is going to be this one mm staff shrink staff  together with this one mm project right but now   we want to know the positions again if we count  the positions one two three four five six seven   we want to connect to the seventh spot and on this  one i want to connect it to one two so it's going   to start out at point seven on that and it's going  to go to point two so we can do that right here   so with this particular brand new shape that we  just created we're going to connect it to our   staff shrink and the staff id this is why it's  important to extract that staff id we need that   staff id remember that was one of the first things  we got up here right after we extracted that staff   id that's very important because i need to know  what connector to have that staff id is right   here i know the rest of it but i need to combine  it to the staff id so i know exactly what shape to   connect it to right if i expand this one i need to  know that it is staff two so getting that staff id   is very important so i know the rest is the same  so i know exactly where to put those connectors in   so we're going to find that staff connector  right here so we're going to put that staff   connector scroll down and find where we were so  here so we're going to connect it to the mm staff   shrink staff and the staff id 0.7 on that and  we're connecting it to the one we just created   in mn project and clicking point 2 right from  7 all the way around here 2.2 so that's going   to connect it right there that's it so now what we  want to do is we want to add the project expanders   right so we've added in the staff connectors now  what i want to do is i want to add in the project   expanded right if i'm going to expand this project  right here i need to add in this so this called   mn project expander project 2 right so how are we  going to do that well the first thing we're going   to use is this sample right here i've created  the sample called project expander sample it's   the same thing just in a different color right  i could have changed the colors with vba so i'm   going to place that directly here i want to place  it so that i can expand that project and then show   all the tasks accordingly so i need to place that  and they also need to duplicate that so adding in   those expanders we're going to take that project  expander that's sample expander we're going to   duplicate it we're giving it a unique name based  on this and the project id then we're going to   do is we're going to focus we're going to place  that directly on the left right i want to place it   on the left position of this plus the width of  this plus a little bit more and i'll place it   directly in the middle just like we did before  so we're placing it based on the left position   of that original shape with the project plus the  width of that shape plus a little bit more plus 8   pixels and i also want to place the top position  based on the top position of that project and id   plus a little bit down 2.5 that's going to drop  it down centered then of course i want to assign   a macro tip there's a macro that we're going  to focus on that macro is going to load these   tasks up so i want to make sure that there's a  macro that's already been assigned to that we're   going to call that mind map load task that is the  macro that's going to load those tasks up great so   now all we need to do is just add this little  connector here again this is very easy we've   got another connector here i'm going to add this  blue one here this one what does that look like   again that's a blue to green so i'm going to use  that same connector here and i'm going to add it   directly in here so when we expand it it's going  to show and then hiding it let's go ahead and add   that expander so that's the connector i want to  add in right now so we're going to do that to do   that we're going to use that project connector  sample we're going to focus on give it a very   also a unique name called project staff connector  project staff connector and then what we're going   to do is we're going to focus on that again i want  to connect it this time we're going to connect it   to point one two three point four all the way to  again one two three two point three so point four   to point three we're connecting it to the project  and the project are connected into this shape here   and then we're going to connect it to this brand  new expander that we just added on point 3.   that's all we need to do so as we loop through  each project we're going to do that and then   we're simply going to increment the project so  that we have a space of 50 pixel space between   all the projects accordingly so that's it that's  all we need to do to create those projects next up   again we need to hide the project remember we also  created this expander and we also assigned a macro   to this called collapse task collapse to actually  this one right here sorry this one right here that   we haven't got into that one yet this one here  right when we expanded those projects we had a   macro here that's been associated this one this  one is called actually the high projects that's   the one i want to go in and what's this going to  do well when we click on that it's simply going   to hide all those projects or actually delete them  so that's the macro that i want to go into now so   first thing what i want to do is i want to reset  all the project if i've clicked project here i   want to make sure that we're resetting all those  projects i want j3 to go to all projects right   because i'm clicking on another staff i want  to make sure that it goes to all projects here   so setting j3 to all projects next up i want to  loop through all the shapes i want to remove any   shapes that are associated with projects and any  shapes that are associated with tasks as we move   through different staff i want to make sure that  all this got removed when we click on that one   so when i click on this here i want to so let's  say i click on here and i've expanded different   here i want to make sure that all when i click on  hiding that i want to make sure that all those get   hidden right so we need to simply loop through  those and look for any shapes that are based on   project or based on text and simply delete them so  we can do that here so we're going to look for any   shapes as we loop through all the shapes in the  sheet look for any shapes that are associated with   those tasks such as the group right group task  group 6 here task group project 2 and remove all   those associated with that and also what i want  to use any expanders right move any sync or may   remove all shrink or staff project buttons here  and also the staff expander make sure that that   is now visible remember we've hidden that expander  right we've hidden it and we move here we hide it   now we need to show that this staff expander and  we need to know that staff id also important there   so we're going to do that expander staff any shape  that contains this we're going to then if it's   greater than 0 we're going to make sure that's  visible showing that okay so that's going to loop   just show that expander i want to make sure that  all these expanders are displayed and none of the   shrink ones so that's going to show them all right  so that's all we have to do to simply then hide   oops simply hide any uh tasks that are associated  or projects that are associated with the task as   we remove that and expand it great so the next  macro that we're going to focus on is the load   tasks now this is the macro when i click on a  specific here it is a macro that's loaded here   that is the macro that's tied to this button if  i right click here and we assign the macro we see   it's called load tasks remember this is the same  macro load task that when we added that here load   task it is the same macro when we created that  expander it's the same marker that we created on   the action we created that expander and that's  the one we're going to go over now low task so   what that's going to do is load all the tasks  now it's a little bit tricky that top position   because i want to make sure that we always have  position properly right if i add this i can set   a reasonable top position for this but if i add  another one i need to bring that top position down   very hard to work with these unless we actually  group these so we're going to make sure that we're   grouping all the tasks associated with the project  and giving it a very specific name on that and   that's going to help us out all right so let's get  into the macro that's going to work with that and   help us do that okay it's called load tasks and  we certainly need some unique variables task id   task name status color right we're coloring the  icon and of course shrink shape as a string i   want the project id name group and array as a  string we're going to be looping through those   i want the task count i want to know how many  tasks the row and last project row the group count   right how many groups we have we need to position  those so i need to know how many groups of tasks   the project row as we loop through the projects  and the total task height i need to determine the   height and i want to make sure that the heights  of these are separated so that they are not all   over each other and that they're separated  accordingly so we need to know that height   put that into variable i also want the task group  as a shape and so we're going to be going through   that all right so the first thing what i want to  do is i want to set the project id based on the   running macro right so basically what i want to  do is when i save a task i want to make an update   so when i that's gonna right so if i let's say i  decide on this i select phone meeting right and   let's say i decide that it's going to be an email  instead right when i make a change that i want to   update that when i click save so i need  to determine notice to change the email   right and so unless if i click here maybe you  missed that right so if i change it back to phone   meeting keep an eye on that i want to update that  i want to click save and i want to update that to   automatically right so i need to know the macro  that called it there's there's one that that   refreshes this based on this save and there's  another one that refreshes it based on this so   there's two different ways that we can run this  so i need to determine and we're going to use   application collar meaning what is the name of  that shape project one i need to know the project   all i need to know is the project id so that i can  determine all the tasks within that project id so   that project id is either going to come from here  this number one here right this is project here   project six right here or it's going to come  here so if the pro so if this project if the   save button was clicked i know that it's going to  be based on project id 1. so as we load different   projects the project id so if i click on here  we see that this is project id 6. if i click   on here we see that this is project id 2. we'll  be getting into that in a little bit later on   so the project id is very very important because i  need to know what tasks to refresh so the project   id is either going to come from here this button  or it's going to come from here and that's going   to be based on whatever button i choose if the  button name that i select is called save button   notice it's the save button so that's the icon  name here if i just click here if i click save   button that is the icon name also the shape  here is also the same name so that means if the   user clicks save i want to i'll refresh this  set of tasks here and that's based on project   id 2 or whatever project we've selected right so  i need to basically differentiate i need to know   is the project id here or is it here right which  one are we doing because there's two ways that we   can refresh there's basically two ways that i can  refresh the task i can refresh it here or i can   refresh it with the save button so that's just  basically it so two ways so i need to determine   which one is used right so if the application  caller is save button we're going to extract that   project id from b2 however if the application  caller contains this right meaning this button   right here let's take a look at these buttons  right here if the if it's called take a look at   this name that's called mm project expand project  six right so if the apple caller contains this   text we know it's the button then how do i get  that project id then the project id is basically   all this subtracting removing all this text and  leaving just the six there that's going to be   our project idea i just need to extract i need  to get that project id it's very very important   if i have that project id then i know what tasks  to put in there so just because i only want   tasks for that individual project so otherwise  else right let's just put the notes save   button click and then we'll call this the project  expander button clicked or the project expander   button project expander button so then we can  get the project id here so how do we do that   we're going to use the application called the  name of the button that clicked it we're going   to remove using the place we're removing the text  part of the button that's going to replacing with   nothing and that's going to leave us exactly what  the project id so two different ways to get the   project based on what button the user has clicked  that way we can refresh those tags regardless if   it's on save or if it's on expanded okay so that's  very very helpful okay so now what i want to do is   i want to delete this project group exists right  if this project group exists i want to delete it   right i want to remove that so in case i click it  so how do we do that well if the project group if   it already exists delete the project group exists  only that project group not all of them right not   every single project group only the one that  exists right only clearing that out only that   specific one and that project group is going to  be based here task group project and the project   one so i only focus on this if it's visible delete  it because i want to make sure it's recreated so   based on this project id now what i want to do is  of course i need and when i expand this i need to   basically take this this expander i want to hide  it and i want to create a brand new shrink one i   want to create a brand new one right on here right  so project string sample i want to duplicate this   and i want to place it directly in here so i want  to have that expanded so to do that the shrink   shape what i'm going to be doing is getting  a name for that i'm going to set the shrink   shape name to project group and the project  id that string shape setting that name okay   now what i want to do is i want to get the  project name i want to know the project name   so the project name is going to be based on this  right i want to know right remember if i if i add   this click here not only do i need the project  id i need the name why is that name important   because that name is important because  i need to place it in the filters right   if i know the project id remember we've extracted  the project id from here or we've gotten it from   here if i know the project id i automatically know  the name because why because our project name is   stored in either one of these shapes so to extract  that all i need to do is take this the text take   the shape and then project plus the project id and  determine whatever the text is inside this shape   that becomes our project name i place that  project name in j and then i can then filter   out using our task and determine only those tasks  that are associated with the individual project   and place them right here so that way the project  name goes here the results are only those tasks   associated with that project and placing it  directly inside here so we need to get that   name so how are we going to get that name all i  need to do is determine the shape name mn project   adding the project id placing the text inside  here so here's how we do it so project name is   going to be the shapes mm project and the project  id remember we're going to take the text frame and   the text from that shape and to put it inside this  variable then all i need to do inside j3 right   here is place that project name put it in that  filter and that's going to filter out only tasks   associated with that once we run our advanced  filter great so now we're ready to now we've   all we've done is we've added in this project  name here we've already got the staff in here   now we're ready to run our advanced filters here  and extracting just unique text based on the same   criteria that we use basing on the same original  data so last row just like we did before with   less than row changing this to j as we had before  and then what we did is i want the same criteria   right same exact one o through s and actually  it should go to four right i'm gonna change   this one up here i think i forgot to change this  one up here to four making sure that we have that   all the way up yeah that's what i thought four so  not 43. okay so fourth that we can get all that i   want to make sure that that's accurate because we  need to include both dates on that okay so moving   down finding my place if i'm lucky enough to  find it creating those tasks right here copy and   this time our results for the task are going to go  directly inside al i want those results to come al   through a and 2. and that's we're going to have  so i want both the task id the task name and i   also want the status because i need to color  accordingly that status is important because i   need to color that icon accordingly right here so  to do that we could we need to color these icons   and fill those icons with that color so to do that  we're going to have those results come there again   pulling the last row based on al and then if  there's no results right if the last results   were less than three then let's let the user  know that no tasks exist for the selected project   and we can exit out of the sub i'm going to turn  off application screen update it's going to make   it a little bit faster because we have to move  those things and now what i'd like to do is i'd   like to sort these right in this case take a look  at this i like the sort i like the idea of having   the sorted based on the same here to do in  progress overdoing completed i want him in   that order that specific order so to do that  right i want notice that they're all in that   order i want to create a custom order and to  do that we're going to use custom orders so   so that's what i want so a and 3 we're going to  set a sort we want to clear the sort fields and   we're going to add a key based on an3 that's going  to be the status right if we take a look at our   results our status is going to come in a and 3 and  that's where i want to sort i want to base it on   this order to do in progress overdue and completed  right that's the order that i want to play it and   date option is normal and i want ascending then  i'm going to set the results based on al3 through   a in the last results row that's going to sort  them based on the status in that order and then   we're going to apply that sort once they're in  that order i want to determine how many tasks very   important i want to determine how many tasks i  know if the last row is 6 and i subtract 2 that we   have a total of 4 tasks so we're going to do that  and set that into a variable called task count and   it's going to be the last row of minus 2 is going  to be the number of tasks i want to get that top   position that top position right i want to know  what are we going to again just like we did before   i'm going to take it based on the project and  the project id kind of paste it on this project   right if i add if i remove this let's remove it  if i set that top position i want to be based   very similar to what we have around here similar  to this right so i'm going to base it on that   but i want a little bit higher than that right  based on that so because i want it closer to the   top otherwise we're going to set up so that top  position is going to be the top position and we're   going to do the task count minus 1 and we're  going to multiply it by 50 the 50 is going to   be the spacing between each task if we want less  spacing we can do that too so 50 right times the   task count minus one divided by two sit in the top  position split rows above the expander okay we're   going to set that left position also based on that  but basically i wanted 80 pixels based on this 80   pixels away setting the the left position moving  it over to the right if it's less than 80 to move   it closer and so on and so forth so it's going to  set the left position okay now what again i want   to make sure that that mind map right that that  top position has a minimum maximum top right if   it's up higher setting the maximum based on row 5  just like we did before so the if the top position   is less than row 5 the top position then set the  row position based on the top position of row 5.   it's going to set the minimum top position just as  we did before and now what i want to do is again   i want to replace that expander with i want to  replace it with that shrink button so again we're   going to do just like we did we're going to take  that string sample we're going to duplicate it   and that's this one right here this one right  here this string sample i'm going to duplicate it   and i need to make sure that it is placed  directly in here it's going to be the same   left position in the same height position as our  expander right but i certainly want to make that   expand i want to hide that expander right when i  when i click here i want to make sure that this   is hidden and the shrink is shown so i'm going  to hide that and show the string and so to do   that we're going to make sure that expander shape  is going to be visible equals ms false we want to   hide that one then all we want to do is take that  shrink the one that we just duplicated right here   and we're going to place it i want to place it  directly in the same location as our expander   the same left position as our expander the  same top position our expander and i also   want to of course assign a macro to that that  macro this given macro is going to actually   be able to hide these tasks so i want to give it  a macro that's going to hide these tasks so to do   that we're going to give it a macro called mind  map collapse task that's going to collapse those   tasks right so we don't see them okay so now we've  basically created this we've added our expander   here we've shown it up now what i want to do is i  want to create the associated shapes for that task   to do that what we want to do is i want to loop  through just like we did the projects just like we   did the staff we're going to loop through all the  tasks associated from three to five so or the last   row we're going to loop through all the tasks that  last results wrote again i want some variables the   task id is going to come from al the task name is  going to come from am and the status is going to   go into variable it's going to come from a n and  the status is exceptionally important because it's   going to pull our colors right i need to look in  here and determine what row the status is on and   then extract the color of the cell and that color  cell is going to come directly from column c when   i locate it now i've also created a name range  called status here so what i want to do is i want   to find that status determine what row it is and  then get the color out of it so that's what we're   going to do if it's not found it could create  an error so we're going to wrap it in on air   resume next and on error go to zero we're going  to set that task row it's going to be based on the   admin status named range we're going to look for  something fine we're going to find that tax status   and i want to extract the row from it okay and  of course if it's not found we would create an   error so we're going to check if the task row is  not 0 then we can determine the task color based   on the row that's found based on column c and the  task row i'm going to get that interior color i'm   going to extract that i'm going to put it into  this variable called task color that's going   to help us out when we want to color that icon so  now we'll get we're ready to do is we're ready to   set up and we've got all of our data we've got all  our variables now what we're going to do is we're   going to create these shapes here again taking our  original shape mind map sample and we're going to   create this shape right here just as we did before  so taking that sample duplicating it giving it a   unique name called mmtask and the task id which  we've already extracted here into the variable   now what we do is we're going to work with it  we're going to set the left position based on the   left position the top position based on the top  position and we're going to give it a text of the   task name right the task name we've already again  auto sizing it text if it's setting the height   right and also what i want to do is i want to add  a space for the tab the width of that is going to   be the width plus that i want to add some for  the icon right i want to add a little bit more   on to the width here so really i want this take a  look at this icon here i want to add another icon   that's going to help us view that task when  i click so i want to add so notice that these   didn't have any extra space but this one i  want to create some extra space because i have   the search icon or the view icon i should say  that's going to allow us to click that icon   and view the individual task so i need additional  18 additional pixels to create it so to do that   we're going to take whatever the current width  is and we're going to add on 18 pixels and that's   going to add the additional space for the view  task icon and now what i want to do is i want to   group again like you said you see that these are  all in a group so what i want to do is i want to   create a string and once i have all of the shapes  that i want i'm going to put those in a string   and i'm going to create a group based on that so  we need to start a string so we're going to call   this our task group string and it's going to  be whatever is in already currently in the   string plus mmtask and the task id and a comma i  want make sure these are all separated by comma   so we've just started our group string basically  all the shapes all these icons all the view icons   all the shapes i want them in a single group  to group them i need the names of those shapes   all in a single string separated by a comma so  we're starting this string out right here i'm   making sure to separate it by a comma okay now  we're ready to add the icon right so that icon   on the left take a look at that now what is this  of course we're starting out here with this one   icon right here called icon three is where we want  one it's gonna come directly from here our task   is icon three and we've already brought it over  here so i'm gonna duplicate icon three i'm gonna   place it directly here and also gonna fill that  icon with the color associated with the status   so we can do that here icon 3 we're going to  duplicate that we're going to give it a name   called mm task icon and the task id then with mind  map shapes task we could focus directly on that   give it a left position the same left position  plus 3 over i want to move it slightly over right   i don't want to do i don't want that icon all  the way over to the two on the left i want it   basically a little bit over to the right so we're  going to move it three pixels to the right of that   stay in the left position the top position we're  going to be based on the top position of the shape   that we just created plus four pixels down it's  going to move it a little bit down and give it a   very specific height of 16 a very specific width  of 16. then what we're going to say is i want the   visible line to be true right i'm just going to  put a line around it right let's refresh that   putting a line around it and i want to color that  line and i also want to color the fill color of   that so the line color is going to be the task  color set the border color and that way if you   want the fill color we could just do the borders  if we wanted this would be kind of interesting   let's take a look at what that looks like i'm  going to comment this out let's say we only wanted   to call it the borders that might be kind of  interesting but it's kind of oops let's fix that   don't we need the task color here we need to do  the task color at least some color here comment   this we don't want to comment the group that group  string is very important all right there we go so   now we see we got the borders colored but not the  fill color but i like how that fill color shows up   a little bit better so we want to uncomment that  out and on close all we need to do is just run   that again and we see that the fill color is here  okay so now we've got the fill color right we're   going to take that fill color putting it with the  task color and then also what i want to do is i   want to again combine i want to create a string  but now what we're going to be doing is notice   we add the shapes up here for our string now we're  going to add these icons taking whatever's already   in the group string adding these individual icons  to our group string and a comma right so adding   these now we got one more icon notice we have one  more icon and that's going to be here our mm task   view icon that is going to be called icon 4 icon 4  is where we're going to coming directly from here   view task that's the icon we set for that and i  want to place that directly here so that's what   we're going to start out so this one mind map  shapes icon 4. we're going to duplicate that   giving it a very specific name called mm task  view icon and the task id again we're going to   place it in the left position based on the same  left position as we did plus the width so we're   going to start out with the left position plus the  entire width of that task minus 17 so basically   the entire width of this minus 17 that way they  all have the same width they'll all have the   same place located there okay and then also the  top position is going to be on the top position   ma plus 3 because i don't want it directly on the  top there and then what we want to do is set a   very specific height a very specific width and of  course i mean they have a very specific macro that   macro is going to enable us to actually view this  task and then make changes accordingly if we like   so giving it the this particular macro which  we will be going over and then also lastly to   our group string adding this view icon along  to our group string so we can now group it   and then now what we want to do is we add in a  connector notice that there's a connector attached   to this right so this time we're going to connect  it with project string project one we're going to   take this connector here task connector sample  here and we're going to be duplicating it   again we're going to be attaching it to these  positions just as we did here and it to the   position 2 here we're going to be putting it there  so first of all with this task connector here task   connector sample we're going to duplicate it we're  giving it a very specific name project project id   and i want the unique project id on this that's  very very important because i'm going to need to   know how to remove it if i have to remove all  the ones associated with that project the task   connector and the task id we need to duplicate  that task connector so then focusing on this right   we're going to set that beginning connector on our  project shrink in our project id that's this shape   right here and we're going to look at again it's  going to be located right here i need to place it   on location seven just no one two three four  five six seven is going to be this one right here   placing it on seven and of course we're going to  do the end connector that end connector based on   the task id that task shape and then position  two right position here again this is position   one and this is position two okay so that's how  we're going to place that accordingly so now that   we have that so now we've placed it we've placed  those connectors and now what we're going to do   is we're going to increment the top position 25  right so there's 25. just to keep in mind remember   we saw a top position of 50. that is going to be  able to separate our groups let's zoom out of here   we got way too much right so i need a separator  to separate the groups of 50 pixels and i need to   separate or separate the individual tasks we're  just going to use 25 pixels right if i were to   increase this to let's say 35 you would see that  the tasks are more spread out accordingly right so   then you see they're more spread out here as we  expand those here they're more spread out but i   think 25 is sufficient enough so we've got 25 so  that's going to have the separation between those   individual tasks so then all we do is just  simply loop once we've added we've incremented   the top position between those tasks we just loop  through all the tasks once we've created all the   tasks and we're building out this group string  now that i've got all the tasks all the icons   all of the view icons everything inside a string  all separated by comma of course i need to then   create a group based on that but the first thing  what i want to do is i want to make sure that we   remove the last comma right so we keep adding  commas and commas and commas but it ends it now   ends with a comma but i don't want that comment  to end so i need to remove the last character of   that string so the task group string we need to  take the left position what do i want the left   position i want basically the left position i want  all the characters inside that string minus except   for the last one so we're going to take the entire  length of that grip string subtracting 1 and we're   going to take only the left of that so basically  all this does is it removes the last comma removes   the last character which is a comma once i have  the correct room all i'm going to do is i'm going   to take that and create an array out of it right  once i have an array i can then create a group   based on that but we need it into an array so task  group array that's already been defined up here   right here called task group array as a string  okay so once we have that we can then group it   accordingly but what i need to do is i need to  split it according to based on those commas so   we can do that here so task group array is equal  to split we're going to split it based on that   comma i'm going to create an array of based on  that string create a task group array once i have   an array i can very easily create a group based on  that so mind map based on shapes range task group   array we're going to create a group we're giving a  very very specific name called task group project   and the project id because these are all the tasks  associated with this project that's why getting   the project id is so important so that we have  the project id and we can then create that group   give it a very specific name great so once we have  done that we can then space out groups accordingly   right we've created the groups we haven't space  it out they could be over and on top of each other   i need to make sure that they're all spaced out  accordingly well that doesn't look very good right   so i need to automatically space them out and what  does that mean that means if i select this task   i have no more room for for another group so  what i need to do is i need to make sure that   this gets moved down accordingly before i created  that one and i need to do it automatically so how   are we going to do that well the first thing  what we're going to be doing is we're going to   again determine the last project row based on a  b right i need to know how many projects right   in this case there's three projects right so if  we take a look at a b this is going to be term   in the last row if the last row is 5 i know that  we have three projects associated first thing we   need to do is get a correct project count so  last project row is going to be based on a b   if that last project row is less than four then  go to end macro right there's no projects right   if it's less than four that means we only have one  project if i only have one project if i only have   one project let's see this one here maybe oh this  one's got one project right if i got one project   it doesn't matter we don't need to space it  everything is just the way it is right but if i   have multiple projects then yes certainly we are  going to have to space them out accordingly one   project won't matter so we don't need to continue  on if it's one project we don't continue we just   exit right and the macro we're going to skip all  the way down here and go to end macro we don't   need to space anything out because it's just one  project okay so what we'll want to do is i want   to determine the top position right what does  that set the initial top position it's going to   be on n5 right just going to set a top position i  want to put that into a variable remember 5 is the   row that is our maximum top position it's going to  be based on row 5 here so we're going to put that   into a variable called mm top and now what i want  to do is i want to loop through all the projects   what i want to do is i want to see of all the  projects which one of them have a group right we   notice that project 2 and project 7 have groups  associated with them so if we look in our tasks   and we look at our project we see that we're  looping through the projects i'm going to check   does project 2 does it contain a do we have a  group set for this project does so i want to know   how many of them right so if i count two projects  that have i know that we have two groups that are   associated with each of those and if they're  visible then i want to determine the height   so we're going to loop through all the projects  and i'm going to look for task groups that are   associated with that project this case we have two  one two with right project seven so we can do that   here i'm going to check run that so four project  equals three to the last project row i'm looping   through the projects right here looping through  these from three all the way to the last row okay   so the project id i need i know is in column a  b and now what i'm going to do is i'm going to   check to see if we have a group associated with  that but if not it could create an error so we're   going to set the task group is going to be equal  to the my map shapes task group and the project id   all i need to do is run a check to see if that  group exists if it doesn't it could create and   make sure you wrap it in honor resume next and on  air go to zero now we're going to run the check   to see if the group exists if not task group is  nothing meaning it does exist not and nothing   cancel each other out basically it's saying  if task group is exists then do what then what   i want to do is i want to determine the total  height i need to know the height of this right   the total height of this and the total height of  this i need to determine the combined height of   all the groups very important what is the combined  height so we're going to set that into a variable   the total task height is equal to whatever the  total task height is plus the height of that group   so we're going to take the height of their group  the height of this group right from the top to   the bottom and put that into a variable so i can  determine okay so i know the height of this group   and i know the height of this group if i know the  total heights i can then place them accordingly   all right so moving back into the code here we'll  want to do so what i want to do is i also want to   keep a count of the groups i want to know how  many groups so the group count is equal to the   group count plus one counting those groups and  i want to set the task group as nothing that's   very important because as i loop through that i  want to clear out the task group and i'm going   to check it again so basically with this loop  here next with this loop we're just getting we're   determining the number of accounts of course i  could count the groups right i know that there's   two visible groups right so how many visible  groups are there how many groups have been created   and i know the total height of all the groups once  i get out of that okay we don't need this this was   not helpful probably don't need that was just a  check i'm going to get rid of that one so what   we're going to do is we're going to set the task  group to nothing we're going to reset those task   groups just clearing it out if the group count  equals one then go to mat right if we have just   one group then there's nothing we can do we don't  need to place it we don't need you know just one   group it's not going to interrupt any other groups  so don't worry okay but if now what we do is i'm   going to run another loop another loop through all  the projects right here right so now we know that   there's two projects i'm going to run another loop  whoops here another loop all the way from three to   last doing another and this time what we're going  to be doing is setting the top position based on   that loop we're going to set that project id again  it's going to be to a b setting another project id   and then also again i'm going to check another  one and this one will check another to see if   the group exists but this time we're setting the  top position based on those groups okay so we're   checking to see if the group if the group exists  then we're going to do the task group the top of   that task group in order what is the top of that  task group the top is going to be based on that   mm top then the top position is going to increase  right i need to increment that top position based   on the top of that group plus the height of that  group plus 30 right so again let's look at this   so the top of this group is going to be based on  the mm top plus the height of this group plus 30   then that sets the depth right if i make it 20  or something it's going to be here right so if   i make it 40 it's going to be down here right so  we're adding additional space right so we can the   separation between those groups we're going to set  the task group as nothing right we're resetting   the task group so what this is going to do is  loop through all the groups and it's going to   place them accordingly so if i if i let's say if  i add 1 here right we set the top position here   but if i add another one it's going to set the top  position here if i had another one it's going to   keep resetting and keep resetting so it loops  through all the groups every single time we create   a group it's going to automatically check for  other groups resetting the height so that there's   always sufficient separation between the group  of tasks and that's all we have to do we just   loop through the project row and then application  screen updating too okay great so that's how we   display the task but now remember there's another  macro that we're going to hide the test right   this macro here is going to hide the task right if  we take a look here right click on here click the   assign macro we see that we have something called  collapse task my map that is the same macro that   we assigned when we created that and that is the  same macro that we're going to go over right now   called mind map collapse tab so the first thing  what we want to do is determine the project id   very very important right if i i need to know  that project id because i need to know what   group to delete right it's going to be task group  project 2. but what i need to do is determine that   project id we can do that all we need to do is  again determine the application caller remove the   text that's going to leave us with that project id  if i know the project id i can remove all of the   particular notices project 2. i can remove all  the lines associated with that and i can remove   the group associated with that as well so that's  what we're going to do extract that project id   looking at that shape the application  call the shape that the user clicked   removing this text and it's going to leave us  with the project id and now what we're going   to do is we're going to remove the group that's  associated with that if it doesn't exist for any   reason it could create an error so we're going to  wrap that in honor resume next so we're going to   take that shape and we're going to delete it now  what we're going to do is i need to delete all of   the lines associated with that and of course they  have mm project 2 in them so mm project n2 task   connector i want to remove all the connectors so  mm project id and the task connector that's why it   was so important to put that project id because  i want to make sure that i'm not removing task   connectors for project 7 i only want to remove  task connectors for project 2 when i hide it   so that's why it's important to get that project  id so that's what we're going to do tap project   project id and task connector if it's greater  than 0 we're going to delete all the connectors   associated for that project specifically and then  again i want to make sure that expander remember   that one that we hid i want to make sure that  this is now visible we hid it earlier so it's   called mn project expander project and then the  project id i want to make that visible here so mm   project spanner and project id visible equals  true and then also we can delete the shape mm   just simply application call it delete that's  going to remove this shrink shape and that's   all okay great so we've gone over that and now  there's another macro that we're going to do   it's called clicking here right remember we give  it a specific name this specific name is called   mmview icon 23 23 is associated with the  task id if we remember here inside the tasks   we were able to extract the task id from here we  were to assign it that specific name right if we   go up here we take a look inside there we see that  we have the task id that was associated with this   right here here we go so we're duplicating that  we created that add task connector and we want   to know the task id that's associated in the view  task the view task button here view task icon here   task id we extracted that and we're going to put  that in with the name so we know the name right   so that means if i click on here this one right  here all i need to do is remove this text and i   know that the task is associated here if i put  that task id directly inside this one here b5   when i put it i can load up that so that's  exactly what we're going to do in the macro   so scrolling down here in the view task let's take  a look at this this one right here view test this   is the macro that we're going to go over this  is the macro that's been already been assigned   to this button here again we're going to take the  task id as a string task row and the task column   i'm going to be looping through i need to load up  all the information from all the way from all the   way here to e we're going to use data mapping and  that basically means i'm going to loop from column   all the way to column 10. i'm going to take  whatever's in the found row and i'm going to   put it in ea now how are we going to get that row  well once i take that task id and i put it here i   want to determine the task row based on that we  can do that with a formula here called taskrow   we have a task named range called task id right  it's a formula here in the name manager if we take   a look at the task id we showed you that i believe  we showed you that before it's based on these task   id so we have a named range based on that so i'm  going to extract the row from that our task id   start in row 4 so we're going to add 3 using the  match it's going to give us a row if i know the   row and then what i want to do is i'm going to  loop through all the columns once i know the row   and i'm going to take that cast name and put it  directly in e8 e8 being right here inside e8 i'm   going to take the staff right the staff is located  right here the staff name inside com d i'm going   to place it directly in e7 so we're going to use  this data mapping so we know exactly what cells to   place it inside the mind map right here close that  out inside right here so that's what we're going   to do inside that macro so the task id is very  important task row and task column first of all   again we need to extract that task id we're going  to use that that view icon button we're going to   ex remove the text and it's going to leave us  with the task id you see a lot of patterns it's   the same thing we do with staff the same thing  we did with projects and the same thing we do   so we have the very familiar way that we're doing  things okay what i'm going to do is i'm going to   take that task id i'm going to place it directly  inside b5 what that's going to do is automatically   going to calculate the row as long as we have a  row if it's blank that means we have an error so   or an incorrect task id which we shouldn't so what  we're going to do is we're going to make sure that   b6 contains a value if it does then we can load  it in if b6 equals mp then please select a correct   task to view we can exit the sub out if we it is  correct we know the task row then all we're going   to do is loop through the task columns from 2 to  10 right we've already got we don't need to start   at 1 because we already have the task id all we  need to do is go from 2 all the way to 10 loading   that information the selected row and bringing it  directly inside here so that's what we're going to   do here for the task column equals 2 to 10 but not  staff id or project id which are generated by vba   what does that mean and that means that here  inside the staff id which is in column three   or the project id which is in column eight i don't  want to load i don't want to bring these into   here or here project id why because these  are formulas right i only when i saving when   i'm saving this i want to put this project  id i want to put it directly inside column   h right here and what i'm saving i want to put  the staff id but when i'm loading it in when   i'm bringing this information in and putting  it here i don't want it because formulas that   generate so i don't want to erase these formulas  so that means that we're going to load up all the   information from column 2 to column 10 except for  column 3 and except for column 8 which is column   c and h right so we don't want to do those things  so we're going to run that loop here if the task   column does not equal 3 and the task column does  not equal 8 then we're going to do is the range   we're going to get that range directly from row  one that row is coming from row one on our task   sheet and we're going to place that directly  inside our tasks right it's gonna be equal to   the range right we're gonna take it directly from  our tasks the task row task column bringing that   directly inside our mind map cells and we're  going to get that range directly from row one   as you've seen so that's it that's all we have  to do to place our tasks directly in and so that   places it directly inside here that's great so we  have all the information here but what if we want   to make a change what if i want to make an update  right what if i want to change that to in progress   on this and i want to say that well that's going  to be with another macro or we're going to change   it maybe it's overdue and we want to save it  right and now we see the design meeting has now   been color changed so that's going to be with the  saved macro and that's going to give me inside our   task macro module here so this is the module that  we're going to go over here called task macros and   we're nearly done okay so what we have is we have  task new right this is the same macro if we decide   to click here it's going to clear out all the  information we're going to put a default status   in here and i want to make sure that the selected  cells here so with that cell we're going to clear   out a bunch of cells we're going to select  to do for e9 it's going to set that initial   status and e6 we're going to select okay so that's  relatively simple but when we load it and we want   to make a change we want to make sure that we're  saving it and i also want to make sure that if   it's a new project or new staff those get added  as well if it is a new how do we know if it's an   existing product project or existing staff or not  well if we take a look here inside our project id   is going to be based on site here it's going to be  based on the select project name so we're going to   do that i want to know the project id so i know  that if it's a new name test right here this is   going to be blank and this is going to be blank  because we cannot extract a project id from a   project that doesn't exist we cannot extract  a row so those are going to be blank because   we've wrapped them on error and we have some data  validation here we want to make sure that when we   want to be able to add add new ones we want to  make sure that showair alert this is not selected   notice that his project name sorted right we  have some sorted project names here it's going   to be based on those project names that are sorted  notice we have this list of project names and i'll   show you how we get to that but they're sorted  here i want to make sure that the airless is right   because we want users to be able to add brand new  projects so we don't want we want to give them the   ability to add items that are not currently in  the list okay so make sure that's there okay so   we have that and so what i want to do is i want to  know i want to know if either b2 or b3 is blank we   know it is a brand new project however if they've  selected something we know that two and three are   done so we've got that here so but first thing  what i want to do is i want to make sure on a new   if they try to save it i want to let them  know that we did project project located   the staff task name and also the status are all  required so please make sure to add a project   staff task name and status before saving this  task how do we know that very easily well we   can use some called something called required  fields right let me just saw this fix that here   and then what we want to do is i want to make  sure so i know that we can use count a and this   tells me that there's only a single field using  counter e6 e7 e8 and e9 of those four fields only   one has been filled in and that's why we return  one however on an existing task if we click here   we see that this is four because all fields have  been filled in so this is an easy way to check   to make sure that all the required fields have  been added values in and so this is four so what   i want to do is i want to make sure that that  is four so if b1 is less than four then let the   user know to please make sure to add in project  staff task name or status before saving or and   status before saving this task exit sub because  the required fields have not yet been added in   okay great so now what i want to do is i want  to know is it a new project or it is an existing   project and b3 is going to tell us that remember  if it's a new project right test b3 is going to   be empty so if b3 is empty we want to let then  we want to let the user know this project has   not been previously added would you like to save  or continue right if i click save it says this   project is not perfect would you like to save and  continue okay no okay so we want to give the user   the ability to dynamically and quickly add brand  new projects on the fly if they say yes then they   can continue down so if they say yes they want  to add it how do we do that right so project row   is going to be the first available project on  the project screen plus one right we have our   projects here i want to know that first available  row getting that and i also want to determine   what our next project id is in this case it would  be 11. how do we know that well we can use this in   b4 to do that max the project id plus 1 1. so that  means if there's an error it's going to return   1. why would there be an error to be in there if  there's no data so we're going to set the maximum   and that's going to get us our project so on a  brand new project that has yet to be entered we're   going to take whatever's in b4 and we're going to  place that directly inside b13 we can do that here   and i also want to take that and i want to place  it directly inside here our selected pro no sorry   no need for that because this is a formula no need  for that because as soon as we place it in a it's   going to automatically calculate automatically  calculated because as soon as we place it in a   this here as soon as we place that project name  directly in b it will automatically be calculated   because we have a named range called project  names as soon as we place it directly in here   okay because we're checking on project names right  so as soon as we add that name here it will that   row will be found and it'll be placed that row  right here so we're checking the project names   right formulas let's get escape out of there  formulas name manager project name looking   down here that is their name range that's located  here so as soon as i add that project name here   dynamically that project row will automatically  be calculated so we're going to do two things   we're adding the project id and we're adding the  project name projects row gonna set that project   id whatever's in b for that next project id  and the name is going to come directly from   e6 and go into column b okay so now that we've  added the important information for that project   we can then focus on that now what i want to do  is i also want to make sure that they're sorted   alphabetically notice that all our nice projects  are started so let's take a look at this let's   see if we add a brand new project let's just say  let's say fredder wants a new kitchen fredder's   kitchen okay we have no project but i want to  save that right and it's going to say do you   want to save this project click yes now i want  that project to now be available inside our drop   down list and i want it to be alphabetically  sorted right so that's very very important   so when i click on dave davidson again i want to  make sure that that freder's kitchen has now been   added here and i want to know that the application  that scope that task has been now assigned to that   brand new one right so i want to make sure that  here not only do i want it added to here i want it   sorted and i also want to be able to hear sorted  there's two different lists why are there two   different lists i've got one list here called  project and another list with all why is that   important because this here this drop down list  here contains all projects and now it contains   also freder's kitchen right so if we go into the  formulas and name manager we see two different   so we have project name let's bring that project  name sorted based on this list i need to populate   this with just the names of the sorted and i also  need one with all right i could use the same row   but it's fine having two different columns is fine  so what i want to do is i want to take that brand   new project that we added and i want to put it  in this list here and then i want to sort this   list and i'm just going to copy this list over  here so that's what we're going to do now so what   we're going to do is determine the last row of the  projects we're going to clear any contents here i   want to clear all the way from no just clearing  those contents making sure that they've all   been cleared out to make sure that we're having a  brand new list we can update that to anthro clear   the previous results we're going to take all the  information from end from whatever is in column b   here and bringing it over to here so we're  bringing that over here once we bring it over   here we're going to copy other projects and then  we're going to sort them based on them right based   on ascending i'm going to start at n3 because it's  going to be based on that project name right here   in n3 and i'm going to base it sort normal and  sort ascending and that range is going to be   n3 through and the last row once i apply it it's  going to be sorted by name i then want to populate   this list as well so all i need to do is just take  this list and bring it over and copy it to here   simply with a value to value exchange 03 through  o in the last row simply and 3 in the last row   that's it that's all we're doing now we're going  to do the exactly the same thing for staff right   if i decide i want to add a brand new staff i can  do that as well right so i have a list of staff   here but let's say freder's son wants to join the  team right so freddie maybe freddie wants to join   and he has not been added yet again we have a  brand new staff how do we know it's a new staff   because we don't have a staff rose associated this  staff has not been previous do you want to add   it yes we do and the staff know is this for the  select project okay that's fine we don't have any   staff because now there's no staff right there's  none no projects no staff for that which is fine   and so now we see that project freder's we need  somewhere if we refresh the staff we see we have   a brand new fred fredericks and freddie freders  had now been assigned that task so we have a brand   new staff of course we didn't add a picture of  that stuff but we also want to add a brand new   staff dynamically too just as we did before and  of course it's going to be based on b9 right as   we added that brand new staff in here we see that  b9 here is empty because that staff has been added   as soon as we select an existing staff we know  that so we're going to check b9 b9 we're simply   matching the staff id and that staff id is based  on the staff name so i'm going to take a look at   the staff name right i want to extract the staff  id from bat but if that staff doesn't exist it's   going to show an error otherwise it's going to  be blank right it's going to be blank based on an   error so we're going to use match the staff name  we have a named range called staff name right here   very similar to what we did take a look at the  staff name we got all the same staff name i've   got a single named range based on staff name i've  also got a sorted list based on staff name here   sorted based on staff and assorted with all right  stored itself with all and why is that important   because we have two ones i've got a staff name  here i want a sorted list based on all the staff   names here actually it's not sorted we should  sort it but i also want to have that available   it should be sorted but it's not how it is okay  not the last one so what i want to do is i want   to make sure that we have a drop down list names  and i also want to have one that's all staff right   if i would decide i want to have all staff here  i want to name dreams that includes all staff   here okay this one looks sorted properly okay so  very good all right continuing on so i want to   make sure that we can do that so we're going to  check for the new staff if b9 is empty we know   it's a brand new staff just like we did with our  projects we want to know do we want to continue   right we this is a brand new staff it's not been  previously added do you want to continue if it's   no we're going to exit the sub again we're going  to get a brand new row if they do want to add that   staff determine the first available row based on  the staff again adding in the staff id based on   b10 b10 we're going to show that next step using  the max formula we can also determine plus one   the next staff id so the next staff idea so i'm  going to take this brand new id and i'm going to   assign it to the first one here and i'm also going  to put in a brand new staff name located in a b   the rest can wait this one the row is not really  important for this particular training there we   don't need that so i just left it in but it's  not necessary for this training continuing on so   a was going to take on that staff id b is going  to take on the staff name in e7 okay now we're   going to sort the staff right i'm going to check  on this issue here sorting the staff i want to   sort based on oh that does look correct just to  make sure that's correct all right so make sure   i think what we need to do is we need to update  the named range on that let's take a look at this   staff so this staff this data validation should be  the is the wrong one that's all it's easy enough   right it's this is based on staff name but i want  staff name sorted right so we do have the correct   so this is gonna be based on so all we need to do  is just use f3 making sure that we have the staff   staff name not project name staff name  sorted that's really the one i want here   so now when we say take a look this is the right  one now everything's sorted right so we have the   so the macros working we just need to do we've got  three different named ranges right we've got one   that's unsorted based on original list we have one  that is sorted based but doesn't include all staff   and we have another one that does include all  the words all staff okay good so that's working   correctly now continuing on what i want to do  now that we've added the staff i want to again   sort the staff by name using the last row again  clearing the contents of the previous just like   we did with projects clearing all the contents  here taking our original data and bringing it   over here copy over data from from b3 through b  in the last row bringing it over into column q   sorting it based on q ascending right and then q  through lasso then once we have it just bringing   over the information copying it directly in  through r so that we have two different columns   one including all staff and one including just  the names just like we did before so that's gonna   dynamically add brand new staff okay now what we  need to do so now we've checked for new projects   and added accordingly check for new staff and  add it accordingly now what i need to know i   need to know is this a brand new task or an  existing task if it is an existing test we   will know automatically based on b6 right new  tasks b6 is going to be empty existing tasks b6   is going to have a have a row associated with that  so continuing on so if b6 value is empty we know   it is a brand new task we can set the task row  based on the first available row inside the tasks   list here and it's going to be first available  we can set that brand new task id we know the   task id is going to based on the max formula task  id plus 1. that's going to set that new task id   so i'm going to take 45 if it's a new task  and i'm going to place it directly in that   first available row located in pro 7 and i also  want to then run our our loop all the way from 2   to 10 and using our data mapping add in all  the other data so we can do that right here so   but if it's a new one getting that first task  row placing the task id and also taking that   task id and also placing it directly inside here  taking that task id and placing it directly into   b5 that new task id that's going to go directly  there great so we have that and what if it's an   existing task of an existing task all i need to  do is extract the task row from b6 okay and now   what we can do is run our loop now what we can  do is we can run take all the data from here   and from let's say taking our staff id taking  our task id taking our project id and bringing   all that information into here staff id everything  else and bring it into your using our data   mapping which is located here using those cells  right here okay great so we have that so tasks   cells task row the task column is equal to  whatever's inside this particular first row   here inside our task now we can save or update  all the data associated with that now apps up all   we need to do again is run the macro to load the  tasks right remember that's the macro that we have   so as soon as i update that let's choose  one with a little bit more data in here   so if i change this right and maybe i change this  to let's say in progress as soon as i save that   data we're going to run the macro that reloads  that and notice the color changed here so just   running it so re-run the macro to load text re  let's run macro to load tasks again and then what   we're going to run this little fade out message  which you saw here this little fade out message   this is the shape that automatically just changes  the transparency until it's gone letting the user   know and that we're done with this right here  just running a loop changing the transparency   of a specific shape called task save message  that's the shape that you just saw and then   eventually it's going to hide it okay great so  lastly and then what we're going to do is the   task delete right if i want to delete a task if i  create a new task and let's create let's create a   task that we can delete it assigning an existing  task giving it a task name test task here so we   know and then we can set the due date of let's  say 5 15 setting a due date actually of 5 30 here   and the description test okay so we're going to  create that we're going to save that here and   now we see that the project's right the staff  is on mark right so let's take a look at mark   here and we see that our beth roof job is where  it's going to be located here so here's the task   that we just created right and now what i want to  do is i want to delete this tile so i'm going to   choose delete are you sure you want to delete the  stats yes and it's going to automatically refresh   everything and it's going to be deleted and we  see that mark mason now doesn't have that first   anymore great so we see that that works so how are  we going to make sure well what we're going to do   is we're going to check are you sure you want to  delete this task giving them the ability to leave   then what i want to do is i want to know has this  path been saved right b6 is going to let us know   it means if i decide to in a project right and i  decide i want to delete this project right away if   it's not been saved meaning b6 is empty deleting  is just simply or she won't delete this yes   and it's going to clear it out where there's  nothing in the database to delete however if   there's a row that's already assigned i will need  to delete the existing row inside the database so   we need to differentiate between that so taskrow  is going to be equal if b6 equals empty we can   skip deleting these lines and go to right here so  if the task row if it has been saved b6 is going   to contain a value we're going to put that into a  variable called taskrow and we're going to delete   that task group on our test data we're going to  delete that entire row next up we're going to   run rerun refresh the macro to load the staff  and enter the task new cool all right i'd also   like to check for overdue i want to automatically  mark tasks as overdue however we're running out   of time so what i'm going to do is i'm going to  save that for our patreon members if you're not   currently on patreon members now is the time to  get on patreon is an incredible platform right   where i do tons of ad tons of stuff in fact if you  want to see something on this that i haven't added   you want me to explain something more clear or  there's something that's not quite working let me   know join our patreon platform every single week  i create a brand new feature fix or focus video   brand new training a brand new download for  our patreon members it starts at just a few   dollars a month and it's a great way to support  this channel well that is it for this week that   is the mind map task manager showing you how to  take tasks project staff in a whole new level   and create this really incredible mind map manager  showing you everything how to create those brand   new staff how to add those projects how to  organize your tasks accordingly and show them   so they don't overlap how to view and update and  how to create this really cool filter thank you   so much for joining me on this extended training  i really appreciate your continued support don't   forget to click that like button comment below  and thanks so much and we'll see you next week

Channel: Excel For Freelancers

Views: 147,895

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Mind Map, Automated Mind Map, Excel Mind Map, Mind Map Excel, Automatic Mind Map, Free Mind Map, Free Mind Map Excel, Mind Map Tasks, Mind Map Projects, Mind Map Application, Free Mind Map Application, Free Mind Map App, Excel Free Mind Map, Mind Mapping, Mind Map Free Excel, Mind

Id: dCbyQFiF334

Length: 139min 34sec (8374 seconds)

Published: Tue Jun 07 2022