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.
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.